CODEWITHSUNDEEP
sqlsymfonydoctrine-orm
user1335895
user1335895

Reputation:

Doctrine NOT EXIST condition with ManyToMany relation

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:

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

Upvotes: 3

Views: 1988

Answers (2)

user1335895
user1335895

Reputation:

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

Upvotes: 4

Jonathan Martínez
Jonathan Martínez

Reputation: 269

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')

Upvotes: 0

Related Questions