title: Doctrine and Postgres arrays
body: <p>I have found that Doctrine arrays types are using serialize/unserialize and store data inside "simple" fields like text, integer.</p>
  
  <p>But I have a project with a postgres database with some fields as arrays (mainly text). I want to use that database with Doctrine. Is there a way to handle that ?</p>
  
  <p>Thanks</p>
  
  <p><strong>EDIT :</strong></p>
  
  <p>ps: i now know that sql arrays can be a good practice, thanks to Craig Ringer.</p>
  
  <p>Maybe i can create a custom type. Is there someone that already made that ?</p>
  
  <p><strong>EDIT 2:</strong></p>
  
  <p>Problem half solved :</p>
  
  <pre><code>&lt;?php
  namespace mysite\MyBundle\Types;
  
  use Doctrine\DBAL\Types\Type;
  use Doctrine\DBAL\Platforms\AbstractPlatform;
  
   /**
    * My custom postgres text array datatype.
   */
  class TEXTARRAY extends Type
  {
       const TEXTARRAY = 'textarray'; // modify to match your type name
  
      public function getSqlDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
      {
          return $platform-&gt;getDoctrineTypeMapping('TEXTARRAY');
      }
  
      public function convertToPHPValue($value, AbstractPlatform $platform)
      {
      // This is executed when the value is read from the database. Make your conversions here, optionally using the $platform.
          return explode('","', trim($value, '{""}') );
      }
  
      public function convertToDatabaseValue($value, AbstractPlatform $platform)
      {
      // This is executed when the value is written to the database. Make your conversions here, optionally using the $platform.
  
          settype($value, 'array'); // can be called with a scalar or array
          $result = array();
          foreach ($set as $t) {
              if (is_array($t)) {
                  $result[] = to_pg_array($t);
              } else {
                  $t = str_replace('"', '\\"', $t); // escape double quote
                  if (! is_numeric($t)) // quote only non-numeric values
                      $t = '"' . $t . '"';
                  $result[] = $t;
              }
          }
          return '{' . implode(",", $result) . '}'; // format      
  
      }
  
      public function getName()
      {
          return self::TEXTARRAY; // modify to match your constant name
      }
  }
  </code></pre>
  
  <p>and the controller</p>
  
  <pre><code>&lt;?php
  
  namespace mysite\MyBundle;
  
   use Symfony\Component\HttpKernel\Bundle\Bundle;
   use Doctrine\ORM\EntityManager;
   use Doctrine\DBAL\Types\Type;
  
   class mysiteMyBundle extends Bundle
  {
      public function boot() {
          Type::addType('textarray', 'mysite\MyBundle\Types\TextArray');
          $em = $this-&gt;container-&gt;get('doctrine.orm.default_entity_manager');
          $conn = $em-&gt;getConnection();
          $conn-&gt;getDatabasePlatform()-&gt;registerDoctrineTypeMapping('TEXTARRAY', 'textarray');
      }
  }
  </code></pre>
  
  <p>I know want to search inside these arrays with queries like 'myword' = ANY(myarrayfield) ... Is there somebody having a clue ?</p>
  
  <p>Somebody on #doctrine2 channel told my to build a custom DQL function, so here it is :</p>
  
  <pre><code>&lt;?php
  use Doctrine\ORM\Query\Lexer;
  use Doctrine\ORM\Query\AST\Functions\FunctionNode;
  
  class ANY extends FunctionNode {
  
      public $field;
  
  
      public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker) {
          return 'ANY(' .
              $this-&gt;field-&gt;dispatch($sqlWalker) . ')';
      }
  
      public function parse(\Doctrine\ORM\Query\Parser $parser) {
          $parser-&gt;match(Lexer::T_IDENTIFIER);
          $parser-&gt;match(Lexer::T_OPEN_PARENTHESIS);
          $this-&gt;field = $parser-&gt;StringPrimary();
          $parser-&gt;match(Lexer::T_CLOSE_PARENTHESIS);
      }
  }
  </code></pre>
  
  <p>BUT, i build my queries with a query builder, with array containing things like </p>
  
  <pre><code>$arr[] = $qb-&gt;expr()-&gt;like($alias.".".$field, $qb-&gt;expr()-&gt;literal('%'.trim($val).'%') );
  </code></pre>
  
  <p>And joining them with</p>
  
  <pre><code>if(count($arr) &gt; 0) $qb-&gt;Where(new Expr\Orx($arr));
  else unset($arr);
  </code></pre>
  
  <p>So, how to use my function ? :\</p>