Search code examples
symfonydoctrine-ormdql

Doctrine DQL IN clause with simple_array

I need to check cpv.text (simple_array type Doctrine2 (1,2,5,6)) for pr.id contained in it. Is there any other way to do this? My query isn't working, cuz I'm using IN clause wrong. Sorry for my English. 

return  $this->getEntityManager()
->createQuery(("SELECT c, cpv, pr, p "
. "FROM AmoMainBundle:Company c "
. "JOIN c.status s "
. "JOIN cpv.param p "
. "JOIN c.country co "
. "JOIN p.props pr "
. "WHERE  EXISTS (SELECT p2 FROM AmoMainBundle:Param p2 "
. "LEFT JOIN p2.props pr2 "
. "LEFT JOIN p2.companyParameterValues cpv2 "
. "WHERE (p2.type >=2 AND pr2.text LIKE ?1 "
. "AND pr2.id IN (cpv2.text) AND p2.name LIKE ?2) "
. "OR (p2.type < 2 AND p2.name LIKE ?2)) "
. "AND (s IS NULL OR s.dateto < CURRENT_TIMESTAMP()) "
. "ORDER BY c.lastupd DESC, "
. "c.name ASC, p.mainPriority DESC" )
                    ->setParameter(1,$text)->setParameter(2, $name);
Solution

  • It's impossible, cuz doctrine store array and simple_array data like text. It converted to PHP array after hydration. All possible types and docs. So need to use full-text search, but my table engine is InnoDB.