Symfony 2 custom DQL request inside an array field

Question

I'm trying to make a custom request in my repository with a WHERE clause inside an array field. I tried something like that, not working, but can better show my problem :

$qb ->andWhere( "p.addresses[:index] = :address" )
    ->setParameter( "index" , $p_idLang )
    ->setParameter( "address" , $p_address );

I'm trying to test a value inside the persisted array in my where clause : @ORM\Column(name="addresses", type="array", nullable=true) — Simon Kreanet
– Simon Kreanet, Commented Jan 12, 2015 at 18:07

acontell · Accepted Answer · 2015-01-12 18:31:21Z

2

Extracted from the documentation about array type:

Maps and converts array data based on PHP serialization. If you need to store an exact representation of your array data, you should consider using this type as it uses serialization to represent an exact copy of your array as string in the database. Values retrieved from the database are always converted to PHP’s array type using deserialization or null if no data is present.

Your query doesn't make sense. You have a few options though:

Retrieve p.adresses and check using php if p.adresses[$index] = $address
Try something much less reliable but that could work:

$val_length = strlen($p_address); $qb ->andWhere( "p.addresses LIKE :indexAddress" ) ->setParameter( "indexAddress" , "%i:$p_idLang;s:$val_length:$p_address%" );

Create a new entity and a relation oneToMany between this entity and the new one.

I'd definetely try option 3. Option 1 isn't an option if the array is big or will become big in the future. I wouldn't go for option 2, but as an experiment could be worth trying.

answered Jan 12, 2015 at 18:31

acontell

6,9321 gold badge22 silver badges33 bronze badges

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

1 Comment

Simon Kreanet Over a year ago

Thanks, the second solution is working fine, this array will never be really big. But int the further i'll probably implement the third solution ! Thanks

Collectives™ on Stack Overflow

Symfony 2 custom DQL request inside an array field

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related