Symfony2 doctrine: How to select users that are not in groups with doctrine query builder

Question

I have symfony2 and fos user bundle installed and I'm trying to reproduce this MySql query in Symfony2 doctrine query builder.

This query should select users not belonging to listed groups.

SELECT *  FROM 
    fos_user_user 
left join 
    fos_user_user_group  on fos_user_user.id = fos_user_user_group.user_id   
    and fos_user_user_group.group_id in (1,2,3,4,5,6,7,8)
where 
    fos_user_user_group.user_id is null

Doctrine manual gives example that checks for user belonging to single group, but no reference when groupId is array of several group ID's. :groupId is not integer, doctrine gives an error.

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

Taken from 14. Doctrine Query Language

Mysql scheme is default FOS User bundle. In simplified manner it looks like this:

CREATE TABLE IF NOT EXISTS `fos_user_user` (
  `id` int(11) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `fos_user_group` (
  `id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `roles` longtext COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:array)',
  `deletedAt` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `fos_user_user_group` (
  `user_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Rvanlaak Rvanlaak · Accepted Answer · 2015-09-15T15:32:05

You can add a count to the query, and add a where count = 0 or having count = 0 after that. This should return the users without a group.

Something like this:

$query = $em->createQuery('SELECT u.id, COUNT(u.groups) as groupCount FROM CmsUser u WHERE groupCount = 0');
$ids = $query->getResult();

Symfony2 doctrine: How to select users that are not in groups with doctrine query builder

1 Answers