phpsymfonyormdoctrineexpression

Excluding NULL column datasets in Doctrine query

I am working on a Doctrine/Symfony project and I am attempting to retrieve results only if a reference is set. The column for the relation can be populated with reference IDs or it can be null if no reference is "set". Currently, I am unable to exclude the datasets with a null column.

$qb = $this->em->createQueryBuilder();

$qb->select('am', 'lb')->from('MyBundle:Brand', 'am')
->leftJoin('MyBundle:XBuyer', 'lb')
->where('lb.id = am.buyer')
->andWhere('am.buyer IS NOT NULL');

Another format I tried:

$qb->select('am', 'lb')->from('MyBundle:Brand', 'am')
->leftJoin('MyBundle:XBuyer', 'lb')
->where('lb.id = am.buyer')
->andWhere('am.buyer != :buyer_id_val')
->setParameter('buyer_id_val', '');

also

$qb->select('am', 'lb')->from('MyBundle:Brand', 'am')
->leftJoin('MyBundle:XBuyer', 'lb')
->where('lb.id = am.buyer')
->andWhere($qb->expr()->isNotNull('am.buyer'));

Buyer is the reference to another table - it is buyer_id in the brand's table, followed by:

$data = $qb->getQuery()->execute(null, Query::HYDRATE_SCALAR);

No idea what I'm doing wrong here.

Solution

  • The problem was that I'm still thinking in the context of the database (other projects) but in the case of using doctrine it was necessary to think in the context of an object - more specific if there is a relation between entities.

    The actual IS NOT NULL expression wasn't the problem - the problem was the actual leftJoin on an entity instead of the relation-"name".

    $qb->select('am', 'lb')->from('MyBundle:Brand', 'am')
->leftJoin('am.buyer', 'lb')
->where('am.buyer IS NOT NULL')
->andWhere('lb.id = am.buyer');

    Thanks guys for all the comments and support in this little timeframe.