CODEWITHSUNDEEP
symfonydoctrine
whitebear
whitebear

Reputation: 12433

Query to get the user list which are belongs to a group

I can select the users which has 'sex = 2' by this sql

createQueryBuilder('s')
->where('s.sex = 2');

How can I select the users which are belonging to group A?

My tables are below.

my user table.

ID | name |sex
1  | bob  |1
2  | kayo |2
3  | ken  |1

my fos_group table

ID | name
1  | student
2  | teacher

my fos_user_user_group

user_id | group_id
1       | 1
2       | 2
3       | 1

it means that

Bob and Ken are belonging to group_1(student)

Kayo is belonging to group_2(teacher)

I would like to select the lists from user table which are belonging to 'student' or 'teacher'

What I want to have is username list belonging to student.

ID | name | sex 1 | bob |1 3 | ken |1

Upvotes: 0

Views: 146

Answers (2)

Ryan
Ryan

Reputation: 5026

You need to do a join first, and then filter on the association property.

$entityRepository
  ->createQueryBuilder('s')
  ->join('s.groups', 'g') // Assuming the association on your user entity is 'groups'
  ->where('g.name = :group')->setParameter('group', 'student');

See http://docs.doctrine-project.org/en/2.0.x/reference/dql-doctrine-query-language.html#joins for examples of filtering on associations with DQL.

Upvotes: 1

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

SELECT
    g.name AS GroupName,
    GROUP_CONCAT(u.name) AS Users
FROM fos_group AS g
INNER JOIN fos_user_user_group AS ug ON ug.group_id = g.ID
INNER JOIN user AS u ON u.id = ug.user_id
GROUP BY g.name

OUTPUT : 

GroupName   |   Users
---------------------------
student     |   bob ,   ken
teacher     |   kayo

Upvotes: 0

Related Questions