Doctrine and Postgres arrays

Question

I have found that Doctrine arrays types are using serialize/unserialize and store data inside "simple" fields like text, integer.

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 ?

Thanks

EDIT :

ps: i now know that sql arrays can be a good practice, thanks to Craig Ringer.

Maybe i can create a custom type. Is there someone that already made that ?

EDIT 2:

Problem half solved :

<?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->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
    }
}

and the controller

<?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->container->get('doctrine.orm.default_entity_manager');
        $conn = $em->getConnection();
        $conn->getDatabasePlatform()->registerDoctrineTypeMapping('TEXTARRAY', 'textarray');
    }
}

I know want to search inside these arrays with queries like 'myword' = ANY(myarrayfield) ... Is there somebody having a clue ?

Somebody on #doctrine2 channel told my to build a custom DQL function, so here it is :

<?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->field->dispatch($sqlWalker) . ')';
    }

    public function parse(\Doctrine\ORM\Query\Parser $parser) {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->field = $parser->StringPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

BUT, i build my queries with a query builder, with array containing things like

$arr[] = $qb->expr()->like($alias.".".$field, $qb->expr()->literal('%'.trim($val).'%') );

And joining them with

if(count($arr) > 0) $qb->Where(new Expr\Orx($arr));
else unset($arr);

So, how to use my function ? :\

SQL arrays are actually a really handy denormalisation that can be a huge performance boost when used wisely and carefully. They just shouldn't be used without good reasons, are less portable, etc. — Craig Ringer
– Craig Ringer, Commented Aug 14, 2012 at 11:44

beberlei · Accepted Answer · 2012-08-22 09:22:38Z

5

Inside the QueryBuilder you can use your custom functions just by writing them:

$qb->where("ANY(a.b)");

Some further information:

You can add custom types to the "doctrine" config section in your Symfony application.
The same holds for the custom DQL function.

See "config:dump-reference DoctrineBundle" command on the CLI for syntax in Symfony 2.1, otherwise on the symfony.com website.

answered Aug 22, 2012 at 9:22

beberlei

2022 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Doctrine and Postgres arrays

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related