Selecting entities associated with two other entities, joining three tables in Doctrine2

Question

I have three types of entities,

1. Users
2. Groups
3. Websites

Users can have Websites, and they also can belong to Groups Each of these entities has a name as well.

Here are my Doctrine2 definitions:

<?php
/** @Entity */
class User
{
    // ...


    /**
     * @Column(type="string",length=255,nullable=false)
     * @var string
     */
    protected $name;

    /**
     * @ManyToMany(targetEntity="Group", inversedBy="users")
     * @JoinTable(name="users_groups")
     */
    private $groups;

    /**
     * @ManyToMany(targetEntity="Website", inversedBy="users")
     * @JoinTable(name="users_websites")
     */
    private $websites;

    // ...
}

/** @Entity */
class Group
{
    // ...

    /**
     * @Column(type="string",length=255,nullable=false)
     * @var string
     */
    protected $name;

    /**
     * @ManyToMany(targetEntity="User", mappedBy="groups")
     */
    private $users;



    // ...
}

/** @Entity */
class Website
{
    // ...

    /**
     * @Column(type="string",length=255,nullable=false)
     * @var string
     */
    protected $name;

    /**
     * @ManyToMany(targetEntity="User", mappedBy="websites")
     */
    private $users;

    // ...
}

So now if I want to find all Users in a group called "Admins", I can do this:

$group = $em->getRepository("Group")->findOneByName("Admins");
$users = $group->users;

I can also get all users that are associated with website "Google.com" by doing:

$websites = $em->getRepository("Website")->findOneByName("Google.com");
$users = $websites->users;

Now if I want to get all users who are in "Admins", and are also associated with website "Google", what can I do?

If I was using TSQL, I would join the three tables, how do I do that in Doctrine?

Must I use DQL? How would the DQL look like?

Answer 1

You should use DQL for this.

SELECT u
FROM User u
JOIN u.groups g
JOIN u.websites w
WHERE
    g.name = :group_name
    AND w.name = :website_name

And the php code to do so:

$dql = '...'; // What i've written above
$query = $em->createQuery($dql);
$query->setParameter('group_name', 'Admins');
$query->setParameter('website_name', 'Google');

$users = $query->getResult();

I encourage you to read the doctrine ORM DQL documentation : http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html

PS: The 2 lines of code you've written, should return groups, and websites, and not users