I have entity that has simple_array field keywords. For example I have entities with values drama, adventure, action, and the other drama, sci-fi. I want to provide action, adventure, and returned first entity. Because it has matching keywords.

If I try searching with IN array, it works only if entity keyword field has only one entry.

$query->andWhere("g.keyword IN (:keywords)")
    ->setParameter('keywords', $keywords);

My question is, how should a query look like, to be able to search by multiple keywords, while field has multiple keywords.

CodePudding user response：

If your prop declared like this:

/**
* 
* @ORM\Column(name="keyword", type="simple_array")
*/
private $keyword;

There is no easy way. simple_array just auto-serialize your php-array data into a serialized string every time you save/update (INSERT/UPDATE) it to database and auto-deserialize it from serialized-string to a php-array each time you read (SELECT)

As long as I know, there's no solution for that with MySQL and doctrine. However, PostgreSQL has some neat features for storing serialized data, but I'm not sure.

The only thing I could imagine is to use LIKE for each single keyword you provide. So if you provide action, adventure SQL would be

SELECT * FROM `your_table` WHERE keyword LIKE '