Highlighting if entity is 'Member of' result set

Question

I have two entities, Client and Group:

class Client {
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\ManyToOne(targetEntity=Group::class, inversedBy="clients")
     * @ORM\JoinColumn(nullable=false)
     */
    private $group;
}

class Group {
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\OneToMany(targetEntity=Client::class, mappedBy="group")
     */
    private $clients;
}

What would be a DQL query I could run where I would get a full list of groups, with a flag to say whether a client with id = 1 is in the group?

If I was to do this in bog-standard SQL, I would do something like the following:

select *, (CASE WHEN c.client_id = 10 THEN 1 ELSE 0 END) as in_group
 from group
 left join client c on c.group_id = group.id

goulashsoup goulashsoup · Accepted Answer · 2021-04-24T08:04:33

$clientId = 10;

/** @var EntityManagerainterface $em */
/** @var list<array{0: Group, 1: int}> */ // vimeo psalm PHPDoc type.
$result = $em
    ->createQuery('
        SELECT
            g,
            CASE
                WHEN c.group = g
                THEN 1
                ELSE 0
            END
        FROM Namespace\To\Group g
            JOIN Namespace\To\Client c
        WHERE c.id = :clientId
    ')
    ->setParameters(['clientId' => $clientId])
    ->getResult(); 

foreach($result as [$group, $hasClientWithId]) {
    assert($group instanceof Group);
    assert(is_int($hasClientWithId));
}

Maybe you can directly select true/false in CASE WHEN.

Highlighting if entity is 'Member of' result set

1 Answers