Translate query from MySQL to Doctrine (with NOT IN)

Question

I need help with translation MySQL to Doctrine query.

There is a query what I want to translate:

SELECT izo_order.id 
FROM izo_order 
WHERE izo_order.id 
NOT IN (SELECT izo_installation.order_id 
        FROM izo_installation 
        WHERE izo_installation.order_id=izo_order.id)
        AND izo_order.client_id = 4

I did something like that, but it seems not to work:

$qb = $this->_em->createQuery('SELECT o
FROM AppBundle:Order o
WHERE o.id
NOT IN (SELECT i.order
        FROM AppBundle:Installation i
        WHERE i.order=o.id)
        AND o.clientBelongsTo = :client_id')->setParameter('client_id', $client_id);

        return $qb->getResult();

CiTNOH · Answer 1 · 2015-07-16T13:05:57.387

0

You query looks good, but Your where clause in the subquery should cause the error. Try it without that:

$qb = $this->_em->createQuery('SELECT o
FROM AppBundle:Order o
WHERE o.id
NOT IN (SELECT IDENTITY(i.order)
    FROM AppBundle:Installation i)
    AND o.clientBelongsTo = :client_id')->setParameter('client_id', $client_id);
$qb->getResult();

If you still have the error, You should post the error.

edited Jul 16 '15 at 13:05

answered Jul 03 '15 at 12:24

CiTNOH

186
1
8

I have still error. [Semantical Error] line 0, col 60 near 'order FROM': Error: Invalid PathExpression. Must be a StateFieldPathExpression. – verdun3r Jul 03 '15 at 13:19
1

Have you tried using `SELECT IDENTITY(i.order)` ? (http://stackoverflow.com/a/14216878) – CiTNOH Jul 05 '15 at 22:45
Thanks ! That was it ! – verdun3r Jul 08 '15 at 09:39

Translate query from MySQL to Doctrine (with NOT IN)

1 Answers1