Symfony + Doctrine: How to find alle users WITHOUT a specific many-to-one value?

Question

A Symfony + Doctrine based project uses a User calls to manage registered users. An additional UserMetadata class is used to handle all kind of additional information (e.g. last login date, has visited page xy, etc.) about a user.

UserMetadata has a unidirectional many-to-one relationship to User (one user can have many linked metadata entries. User class does not know about the metadata).

class UserMetadata {
    ...

    /**
     * @ORM\ManyToOne(targetEntity="AppBundle\Entity\User")
     * @ORM\JoinColumn(name="user_id", referencedColumnName="id", onDelete="CASCADE")
     */   
    protected $user;

    protected $key;
    protected $value;

A metadata entry is stored as key/value pair. For example, the date a user last visited a certain page is stored as key='somePageName', value='2020-12-01' is added. If no entry with key='somePageName' exists, the user has not visited this page yet.

Goal 1: Create a query which selects all users which have NOT visited a certain page.

Finding the users who HAVE visited a page is no problem using a LEFT JOIN:

$qb = $this->em->createQueryBuilder();
$qb->select('u')
    ->from('AppBundle\Entity\User', 'u')
    ->andWhere('u.someValue = :filter1')
    ->setParameter('filer1', 'someFilterValueInUserEntity')

    ->leftJoin(UserSettingsEntry::class, 's', 'WITH', 's.user=u.id')
    ->andWhere('s.key = :key')
    ->setParameter('key', 'somePageName');

But how to find users WITHOUT such an entry?

Goal 2: Create a query which selects all users which have NOT visited a certain page, OR where the page was visited more than 6 weeks ago.

Is this possible in the one query?

score 0 · Answer 1 · answered Dec 18 '20 at 16:50

You can use ->notIn() method:

Get your original query results:

$myUsers = $qb->select('u')
    ->from('AppBundle\Entity\User', 'u')
    ->andWhere('u.someValue = :filter1')
    ->setParameter('filer1', 'someFilterValueInUserEntity')

    ->leftJoin(UserSettingsEntry::class, 's', 'WITH', 's.user=u.id')
    ->andWhere('s.key = :key')
    ->setParameter('key', 'somePageName')
    ->getQuery()
    ->getResult();

Then you can use your result to filter a new query:

$qb->select('u')
    ->from('AppBundle\Entity\User', 'u')
    ->where($qb->expr()->notIn('u.id', $myUsers))
    ->getQuery()
    ->getResult();

You can check this question and Doctrine references

Julien B. · Answer 2 · 2020-12-20T04:17:55.103

Since you are using a left join, you can simply check if the right side table entry is null, make sure to move the s.key = :key condition inside the join condition though.

$qb = $this->em->createQueryBuilder();
$qb
    ->select('u')
    ->from('AppBundle\Entity\User', 'u')
    ->andWhere('u.someValue = :filter1')
    ->setParameter('filer1', 'someFilterValueInUserEntity')
    ->leftJoin(UserSettingsEntry::class, 's', 'WITH', $qb->expr()->andX(
        's.user = u.id',
        's.key = :key'
    ))
    ->setParameter('key', 'allo')
    ->andWhere($qb->expr()->isNull('s.id'))
;

Symfony + Doctrine: How to find alle users WITHOUT a specific many-to-one value?

2 Answers2