Doctrine DQL LEFT JOIN set difference

Question

I have the following SQL query:

SELECT DISTINCT u.id
    FROM User u
    LEFT JOIN usergroup_user ug1 ON u.id = ug1.user_id
    LEFT JOIN usergroup_user ug2 ON ug1.user_id = ug2.user_id AND ug2.usergroup_id = :groupid
    WHERE ug2.usergroup_id IS NULL

What this does is return all the users that are not in usergroup :groupid. If the users have records for other groups in addition to the excluded group, I don't want those either.

The SQL query works, but is there a way to write this query in DQL, Doctrine for Symfony2?

I have tried this:

SELECT DISTINCT u.id
    FROM AcmeDemoBundle:User u
    LEFT JOIN u.groups ug1
    LEFT JOIN u.groups ug2 WITH ug1 = ug2 AND ug2 = :groupid
    WHERE ug2 IS NULL

but it doesn't work. The DQL statement is translated into the following SQL statement:

SELECT DISTINCT u0_.id AS id0
    FROM User u0_
    LEFT JOIN usergroup_user u2_ ON u0_.id = u2_.user_id
    LEFT JOIN UserGroup u1_ ON u1_.id = u2_.usergroup_id
    LEFT JOIN usergroup_user u4_ ON u0_.id = u4_.user_id
    LEFT JOIN UserGroup u3_ ON u3_.id = u4_.usergroup_id AND (u1_.id = u3_.id AND u3_.id = :groupid)
    WHERE u3_.id IS NULL

That's obviously not good, because it straddles the join tables and messes up the sets.

In math terms, using sets, I'm doing this: result = A\B (element that are in A but not in B), with A = "all the users", and B = "all the users in group :groupid".

Is what I want to do possible using DQL, or should I just use SQL for this case? Can it be done without turning the join tables into entities of their own?

Test data

ORM schema:

• Resources/config/doctrine/User.orm.yml

  Acme\DemoBundle\Entity\User:
    type: entity
    table: null
    fields:
      id:
        type: integer
        id: true
        generator:
          strategy: AUTO
    manyToMany:
      groups:
        targetEntity: UserGroup
        mappedBy: users
  

• Resources/config/doctrine/UserGroup.orm.yml

  Acme\DemoBundle\Entity\UserGroup:
    type: entity
    table: null
    fields:
      id:
        type: integer
        id: true
        generator:
          strategy: AUTO
    manyToMany:
      users:
        targetEntity: User
        inversedBy: groups
  

SQL data:

INSERT INTO `User` VALUES (1),(2),(3),(4);
INSERT INTO `UserGroup` VALUES (1),(2),(3),(4);
INSERT INTO `usergroup_user` VALUES (1,1),(1,2),(1,3),(2,2),(3,4);

Answer 1

I think I found the answer just after I posted, in a "related" link in the sidebar, which mentioned MEMBER OF in a possibly non-related context.

My DQL now looks like this:

SELECT DISTINCT u.id
    FROM AcmeDemoBundle:User u
    WHERE :group NOT MEMBER OF u.groups

Which translates to this:

SELECT DISTINCT u0_.id AS id0
    FROM User u0_
    WHERE NOT EXISTS (
        SELECT 1
        FROM usergroup_user u1_
        INNER JOIN UserGroup u2_ ON u1_.usergroup_id = u2_.id
        WHERE u1_.user_id = u0_.id AND u2_.id = :group
    )

It looks like it's doing the right thing too.

This is one of those answers that one finds by chance, just after posting to StackOverflow after not finding anything by searching Google and StackOverflow for a few days :)

Fun Fact: You can't run this DQL in Symfony2's ./app/console with doctrine:query:dql - at least it gives me an error when I try replacing :group with an ID.