Individual column searching subquery syntax error

[johnzuk]

I tested your bundle with this code:

return $this->get('datatable')->setEntity('CRMBundle:Company', 'x')->setFields([
            'name' => 'x.name',
            'street' => 'x.street',
            "surname"         => '(SELECT MIN(u.surname) FROM CRMBundle:Contact u WHERE u.company = x.id) as surname',
            "_identifier_"  => 'x.id'
        ])->setSearch(true)->setSearchFields(array(0, 2))->setGlobalSearch(true);

and if i tried to filtering by surname I got this error: [Syntax Error] line 0, col 164: Error: Expected =, <, <=, <>, >, >=, !=, got 'LIKE' The QueryException: SELECT COUNT(x.id) FROM CRMBundle:Company x WHERE (SELECT MIN(u_840532410.surname) FROM CRMBundle:Contact u_840532410 WHERE u_840532410.company = x.id) LIKE :sSearch_2 AND (x.name LIKE :sSearch_global_0 OR x.street LIKE :sSearch_global_1 OR (SELECT MIN(u_840532410.surname) FROM CRMBundle:Contact u_840532410 WHERE u_840532410.company = x.id) = :sSearch_global_2)

[waldo2188]

Try to give an alias that is different than the original name

return $this->get('datatable')->setEntity('CRMBundle:Company', 'x')->setFields([
            'name' => 'x.name',
            'street' => 'x.street',
            "surname"         => '(SELECT MIN(u.surname) FROM CRMBundle:Contact u WHERE u.company = x.id) as filtred_surname',
            "_identifier_"  => 'x.id'
        ])->setSearch(true)->setSearchFields(array(0, 2))->setGlobalSearch(true);

[johnzuk]

Still nothing :(

[waldo2188]

Try to trace in the bundle code where it failed.

[johnzuk]

The problem is in doctrine, because if i try to create the same query in query builder:

$this->getDoctrine()->getManager()
            ->getRepository('ServalCRMBundle:Company')
            ->createQueryBuilder('c')
            ->select('COUNT(c.id)')
            ->where("(SELECT MIN(u.surname) FROM ServalCRMBundle:Contact u where u.id = c.id) LIKE '%a%'")
            ->getQuery()
            ->getResult()

i got the same error

[waldo2188]

Ok, you can debug your request by dumping the DQL and the SQL of the request and see whats wrong.

$rqt = $this->getDoctrine()->getManager()
            ->getRepository('ServalCRMBundle:Company')
            ->createQueryBuilder('c')
            ->select('COUNT(c.id)')
            ->where("(SELECT MIN(u.surname) FROM ServalCRMBundle:Contact u where u.id = c.id) LIKE '%a%'");

dump($rqt->getDQL());

dump($rqt->getQuery()->getSql());