Doctrine QueryBuilder – CASE WHEN

I have a question about case when in doctrine. The problem with this is when uim.isProfileImage is 1 for a user, that returns that row and every other row in user_image table for that user. What I want is, if there is a profile image (isProfileImage is 1 in user_image table, there can be only one row with that value for same user) give me only that row, and if there isn’t something like that give me null for that.

public function searchUsersByIds(array $ids)
{
    $qb = $this->createQueryBuilder('user');
    $qb->select('user.id', 'user.username');
    $qb->addSelect('(CASE WHEN uim.isProfileImage = :isProfileImage THEН uim.imageFileName ELSE :null END) AS imageFileName');
    $qb->leftJoin('user.userImages','uim');
    $qb->add('where', $qb->expr()->in('user.id', ':ids'));
    $qb->setParameter('ids', $ids);
    $qb->setParameter('isProfileImage', 1);
    $qb->setParameter('null', null);
    return $qb->getQuery()->getResult();
}

So it should look something like this: With profile image:

userId => 1,
username => 'Mark',
imageFileName => 'someFileName'

And without profile image:

userId => 1,
username => 'Mark',
imageFileName => null

Also since there is an in expr, it should work with multiple ids that pass to this function. Thanks in advance.

Answer

Try this:

public function searchUsersByIds(array $ids)
{
    $qb = $this->createQueryBuilder('user');
    $qb->select('user.id', 'user.username');
    $qb->addSelect('uim.imageFileName AS imageFileName');
    $qb->leftJoin('user.userImages','uim', 'with', 'uim.isProfileImage = :isProfileImage');
    $qb->add('where', $qb->expr()->in('user.id', ':ids'));
    $qb->setParameter('ids', $ids);
    $qb->setParameter('isProfileImage', 1);
    $qb->setParameter('null', null);
    return $qb->getQuery()->getResult();
}

Advertisement

Answer