CODEWITHSUNDEEP
phpmysqlsymfonydoctrine-ormmany-to-many
Ema.jar
Ema.jar

Reputation: 2424

Query on a many-to-many relationship using Doctrine with Symfony2

I'm triyng to understand how the many to many relationship works with Doctrine and Symfony2.

I've recreated the example shown in the official documentation (goo.gl/GYcVE0) and i have two Entity Classes: User and Group as you can see below.

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

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

    public function __construct() {
        $this->groups = new \Doctrine\Common\Collections\ArrayCollection();
    }

    // ...
}

/** @Entity **/
class Group
{
    // ...
    /**
     * @ManyToMany(targetEntity="User", mappedBy="groups")
     **/
    private $users;

    public function __construct() {
        $this->users = new \Doctrine\Common\Collections\ArrayCollection();
    }

    // ...
}

If i update my DB i get this MySQL Schema:

CREATE TABLE User (
    id INT AUTO_INCREMENT NOT NULL,
    PRIMARY KEY(id)
) ENGINE = InnoDB;
CREATE TABLE users_groups (
    user_id INT NOT NULL,
    group_id INT NOT NULL,
    PRIMARY KEY(user_id, group_id)
) ENGINE = InnoDB;
CREATE TABLE Group (
    id INT AUTO_INCREMENT NOT NULL,
    PRIMARY KEY(id)
) ENGINE = InnoDB;
ALTER TABLE users_groups ADD FOREIGN KEY (user_id) REFERENCES User(id);
ALTER TABLE users_groups ADD FOREIGN KEY (group_id) REFERENCES Group(id);

The problem is that in Symfony2 I need the Entity to generate a query and in this case I don't have an Entity associated to the table users_group because this table is created automatically by the framework.

So, how can I retrieve the information related to this relationship table? For example I need to get all the Users in a Group which are the users that have an id that appears in the table users_group.

How can I do that using the DQL, QueryBuilder or other methods?

Thanks a lot.

Upvotes: 23

Views: 35623

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You can write a join DQL query as below

$em = $this->getContainer()->get('doctrine')->getManager();
$repository = $em->getRepository('YourNamespaceYourBundle:User');
$query = $repository->createQueryBuilder('u')
    ->innerJoin('u.groups', 'g')
    ->where('g.id = :group_id')
    ->setParameter('group_id', 5)
    ->getQuery()->getResult();

Your mapping for groups property in User entity will handle join part itself you don't have to mention the junction table in your DQL query

Upvotes: 38

Related Questions