23

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.

Ema.jar
  • 2,370
  • 1
  • 33
  • 43
  • What is your criteria can you write mysql query for your criteria ? so we will try to translate it in DQL query – M Khalid Junaid Oct 24 '14 at 13:47
  • 1
    @M Khalid Junaid For example i need to get all users in a group. Using raw SQL should be something like SELECT u.id, u.name FROM Users u, Group g, users_group ug WHERE ug.group_id=5 AND u.id=ud.user_id. As you can see i need the Entity related to the table users_group. – Ema.jar Oct 24 '14 at 13:54

1 Answers1

38

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

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
  • What should be query if have group name and not group id ? – Vidhi Sep 02 '15 at 01:29
  • 1
    @Vidhi adjust the where part with group name something like `->where('g.name= :group_name')->setParameter('group_name', $group_name)` – M Khalid Junaid Sep 02 '15 at 05:09
  • 1
    Hey i posted a [question](http://stackoverflow.com/questions/34874485/how-does-inner-join-work-on-a-many-to-many-relationship-using-doctrine-and-symfo) referencing your answer, i needed an explanation on how innerJoin works, you might be interested – pinch boi triggered af Jan 19 '16 at 10:42
  • 1
    Exactly what I needed to understand how Doctrine handle manytomany relationships, thank you, a lot. – Stéphane Feb 19 '16 at 12:33