Symfony2 - Doctrine DQL - select rows that are not related to another table row

Question

I have two tables Offer and OfferSuggestion that has FOREIGN_KEY and is related to Offer. I would like to get rows of Offer that are not yet related to any row of OfferSuggestion.

Offer:

id      name
1       offer1
2       offer2

OfferSuggestion:

id    offer_id   name
1     2          suggestion2

In this case I should get offer1 that does not have suggestion.

So far I have tried this, but is not working:

$query = 'SELECT o FROM IndexBundle:Offer o '.
    'WHERE NOT EXISTS ('.
    'SELECT s.offer FROM IndexBundle:OfferSuggestion s '.
    'WHERE o.id = s.offer)';
$query = $em->createQuery($query);

I get an error:

[Semantical Error] line 0, col 91 near 'offer FROM IndexBundle:OfferSuggestion': Error: Invalid PathExpression. Must be a StateFieldPathExpression.

Any ideas what I am missing?

Answer 1

You could simply refer to the object instance as follow:

$query = 'SELECT o FROM IndexBundle:Offer o '.
    'WHERE NOT EXISTS ('.
    'SELECT s FROM IndexBundle:OfferSuggestion s '.
    'WHERE o = s.offer)';

---

You could also create the subquery with a querybuilder:

As example, only for demonstrate HOW-TO use a subquery select statement inside a select statement, suppose we what to find all user that not yet have compile the address (no records exists in the address table):

 // get an ExpressionBuilder instance, so that you
$expr = $this->_em->getExpressionBuilder();

// create a subquery in order to take all address records for a specified user id
$sub = $this->_em->createQueryBuilder()
    ->select('a')
    ->from($this->_addressEntityName, 'a')
    ->where('a.user = u.id');


$qb = $this->_em->createQueryBuilder()
    ->select('u')
    ->from($this->_userEntityName, 'u')
    ->where($expr->not($expr->exists($sub->getDQL())));

return $qb->getQuery()->getResult();

Answer 2

Does this work:

$query = 'SELECT o FROM IndexBundle:Offer o '.
    'WHERE NOT IN ('.
    'SELECT s.offer_id FROM IndexBundle:OfferSuggestion s '.
    'WHERE o.id = s.offer_id)';

I'm just guessing, but can you try it?