Doctrine NOT EXIST condition with ManyToMany relation

Question

I have 2 entities with a many-to-many relation as this:

User 
{
    /**
     * @var Group[]
     *
     * @ORM\ManyToMany(targetEntity="Group", inversedBy="users")
     * @ORM\JoinTable(
     *  name="user_group",
     *  joinColumns={
     *      @ORM\JoinColumn(name="user_id", referencedColumnName="id")
     *  },
     *  inverseJoinColumns={
     *      @ORM\JoinColumn(name="group_id", referencedColumnName="id")
     *  }
     * )
     */
    protect $groups
}

Group
{
    /**
     * @var User[]
     *
     * @ORM\ManyToMany(targetEntity="User", mappedBy="groups")
     */
    private $users;
}

This creates 3 tables on DB:

user
group
user_group

Is possible to manipulate user_group in a DQL? I'm trying something like this:

SELECT *
FROM user u
WHERE EXISTS
(SELECT ug.user_id FROM user_group ug WHERE ug.user_id = u.id AND ug.group_id = 3)

This doesn't work since it should be written as Entities. I could run this with Native SQL but I wanted to be sure that there's no better solution for this

May be this is what you are looking for http://stackoverflow.com/questions/10030538/query-with-exists-for-doctrine-symfony2 — yskkin, Mar 27 '17 at 07:52
I'm not really sure how to use it with many-to-many relations, because this won't work `SELECT g FROM group g WHERE g.user = u.id AND g.id = groupId` — , Mar 27 '17 at 08:09
Your native SQL says sub query is based on user_group not group. so subquery DQL looks like `SELECT ug FROM UserGroup ug WHERE ug.user_id = u.id AND ug.group_id = :groupId` — yskkin, Mar 27 '17 at 08:13
Yes, exactly. But the user_group table doesn't have a UserGroup entity mapped, it's a pivot table. So it won't find `UserGroup` — , Mar 27 '17 at 08:16
nimrod188, your SQL is equal to simple query with a JOIN. You're making it more complicated than it is. — Jakub Matczak, Mar 27 '17 at 08:22

score 4 · Accepted Answer · answered Mar 27 '17 at 08:32

The solution from Jonathan Martinez worked but I found this to be more straightforward (specially because I could use NOT MEMBER OF as well):

$query = $em->createQuery('SELECT u.id FROM User u WHERE :groupId MEMBER OF u.groups');
$query->setParameter('groupId', $group);

More on this, here:

http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#dql-select-examples

score 0 · Answer 2 · answered Mar 27 '17 at 08:15

0

You can try this:

$em = $this->getDoctrine()->getManager(); 
$query = $em->createQueryBuilder('u')
        ->select('u')
        ->from('AppBundle:User', 'u')
        ->innerJoin('u.groups', 'g', 'WITH', 'g.user_id = u.id')

answered Mar 27 '17 at 08:15

Jonathan Martínez

269
4
14

You're missing condition `group_id = 3`, and also `'g.user_id = u.id'` is redundant because Doctrine will handle relation condition on its own when you're using `WITH` as `conditionType`. – Jakub Matczak Mar 27 '17 at 08:20

Doctrine NOT EXIST condition with ManyToMany relation

2 Answers2