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 );

Answer 1

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:

1. Retrieve p.adresses and check using php if p.adresses[$index] = $address
2. 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%" );

3. 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.