doctrine queryBuilder= how to find all users who do not have a specific date-CodePudding

I have a entity "user" and a other entity "date" and a entity dateType". A user has zero or more dates. I am looking for all users who do not have a specific date.

e.g. live users:

 ->leftjoin('u.dates','d')
 ->leftJoin(
                'd.dateType',
                'date_type',
                Join::WITH,
                'date_type.identifier <> :dem')
->setParameter('dem',"DEAD")

How to do this? Thanks very much Cedric

CodePudding user response：

This little snippet might get you back on track.

$result = $this
    ->createQueryBuilder('u')
    ->select(['u.id', 'u.username', 'd.identifier'])
    ->leftJoin(DateTable::class, 'd', Join::WITH, 'd.user_id = u.id')
    ->where('d.identifier IS NULL')
    ->getQuery()
    ->getArrayResult();

CodePudding user response：

Solution:

  $qb2 = $this->createQueryBuilder('user');
  $qb2->select('user.id')
      ->leftJoin('user.dates', 'dates')
      ->leftJoin('dates.dateType', 'date_type')
      ->where('date_type.identifier = :dem');

  $qb = $this->createQueryBuilder('u');
  $qb->where($qb->expr()->notIn('u.id', $qb2->getDQL())
  );
  $qb->setParameter('dem', "DEAD");