Doctrine QueryBuilder - CASE WHEN

Question

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.

I think you may want to use a join with a condition: https://stackoverflow.com/questions/19185587/left-join-on-condition-and-other-condition-syntax-in-doctrine — Raress96, Sep 09 '19 at 06:09
Thanks so much, this worked, but I used the solution from down there. :D — LudiDinSaSalaša, Sep 09 '19 at 16:36

score 0 · Accepted Answer · edited Sep 09 '19 at 10:20

0

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();
}

edited Sep 09 '19 at 10:20

Miss Chanandler Bong

4,081
10
26
36

answered Sep 09 '19 at 08:02

akinfiyev

16
3

Thank you so much, you saved me a lot of time. :D – LudiDinSaSalaša Sep 09 '19 at 16:37

Doctrine QueryBuilder - CASE WHEN

1 Answers1