Crafting a Doctrine subquery that hinges on a ManyToMany Relationship using the QueryBuilder

Question

Trying to shake some rust off, and understand how I'd do this with the query builder! I've tried a few approaches, and was met with all kinds of cryptic errors. Haven't visited this way of doing things for awhile, and appreciate the help!

I have a User entity, that belongs to Groups:

/**
 * @ORM\Entity
 * @ORM\Table(name="users")
 * 
class User {

    /**
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     * @ORM\Column(type="integer", nullable=false, options={"unsigned"=true})
     */
    private $id;

    /**
     * @ORM\ManyToMany(targetEntity="Group", cascade={"persist"})
     */
    private $groups;
}


/**
 * @ORM\Entity
 * @ORM\Table(name="`groups`")
 *
 */
class Group {
    /**
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     * @ORM\Column(type="integer", nullable=false, options={"unsigned"=true})
     */
    private $id;
}

Now, I also have a separate GroupsCerts class, which is a list of certifications that are required by specific groups.

/**
 * @ORM\Entity
 * @ORM\Table(name="groups_certs")
 * This entity contains meta data about group-certification relationship
 *
 */
class GroupCertRecord
{
    /**
     * @var Group
     * @ORM\Id
     * @ORM\ManyToOne(targetEntity="Group")
     * @ORM\JoinColumn(onDelete="cascade")
     */
    protected $group;

    /**
     * @var Certification
     * @ORM\Id
     * @ORM\ManyToOne(targetEntity="Certification", inversedBy="group_records")
     * @ORM\JoinColumn(onDelete="cascade")
     */
    protected $cert;

    /**
     * @var \DateTime
     * @ORM\Column(type="datetime", nullable=false, options={"default": "CURRENT_TIMESTAMP"})
     */
    private $time_added;
}

The task is to get all users, that belong to groups, to which a specific certification is indexed.

Simple SQL

I'm currently using the ResultSetMappingBuilder very nicely, the gist of the SQL using that approach is very simple. E.g., looking for certificate 3, the SQL is:

SELECT U.*
FROM users U
WHERE U.id IN (
   SELECT user_id 
   FROM users_groups UG
   WHERE UG.group_id IN (
       SELECT id 
       FROM groups G 
       WHERE G.id IN (
           SELECT group_id 
           FROM groups_certs GC 
           WHERE GC.cert_id=3
       )
   )
)

I had tried to map this into a QueryBuilder expression, but it's dying at the expression builder for "IN". It doesn't like something I am doing - quick solve?

My Failed Attempt

    $em = $this->getEntityManager();
    $expr = $em->getExpressionBuilder();

    $subQuery = $em->createQueryBuilder()
        ->select('(gc.group)')
        ->from(GroupCertRecord::class, 'gc')
        ->where('gc.cert = :cert');

    $queryD = $em->createQueryBuilder()
        ->select('u')
        ->from(User::class, 'u')
        ->where($expr->in('u.groups', $subQuery->getDQL()))
        ->setParameter('cert', $cert);

Thanks!

score 0 · Answer 1 · answered Jan 29 '21 at 18:50

I believe you can achieve the same using joins instead of sub queries and the query in plain SQL would look like

select u.*
from users u
join users_groups ug on u.id = ug.user_id
join groups g on g.id = ug.group_id
join groups_certs gc on g.id = gc.group_id
join certification c on c.id = gc.cert_id
where c.id = 3

These join queries are very handy to transform in DQL or query builder as compared to sub queries, In DQL you utilize the entity mappings in join clause and rest doctrine will take care of tables that are behind the mappings like u.groups points to many to many relation with junction table and g.groupCertifications will handle one to many mapping and so on

Apart from your question you have almost provided all the necessary mappings except how Group relates to GroupCertRecord I would guess it will be something like

class Group
{
    // ...
    
    /**
     * @ORM\OneToMany(targetEntity="GroupCertRecord", mappedBy="groups")
     */
    private $groupCertifications;
}

Your DQL will look like

SELECT u
FROM  AppBundle\Entity\User u
JOIN u.groups g
JOIN g.groupCertifications gc
JOIN gc.cert c
WHERE c.id = :id

And query build will be

$this->createQueryBuilder('u')
    ->select('u')
    ->join('u.groups', 'g')
    ->join('g.groupCertifications', 'gc')
    ->join('gc.cert', 'c')
    ->where('c.id = :id')
    ->setParameter('id', $certId)
    ->getQuery()
    ->getResult();

Crafting a Doctrine subquery that hinges on a ManyToMany Relationship using the QueryBuilder

Simple SQL

My Failed Attempt

1 Answers1