Mysql: join across five tables

Question

I have a mysql database with this setup (omitting fields not relevant to this question)

users
  id                #primary key

user_group_teachers
  id                #primary key
  teacher_id        #foreign key to users.id
  user_group_id     #foreign key to users_groups.id

user_groups
  id                #primary key

user_group_members
  id                #primary key
  pupil_id          #foreign key to pupils.id
  user_group_id     #foreign key to users_groups.id

pupils
  id                #primary key

I have a collection of user ids in an array, called "user_ids".

For each of those user ids, i want to collect the pupil ids associated with that user via the

user -> user_group_teachers -> user_groups -> user_group_members -> pupils

association. Ie, some kind of join across the tables.

So, i'd like to get some kind of result where the rows look like

[1, [6,7,8,9]]

where 1 is the teacher id, and [6,7,8,9] are the ids of pupils. I'd only like each pupil id to appear once in the second list.

Can anyone tell me how to do this in as small a number of queries as possible (or, more broadly, as efficiently as possible). I will probably usually have between 1000 and 10,000 ids in user_ids.

I'm doing this in a ruby script, so can store the results as variables (arrays or hashes) in between queries, if that makes things simpler.

Thanks! max EDIT for Lyhan

Lyhan - thanks but your solution doesn't seem to work. For example in the first row of the results, using your method, i have

| user_id | group_concat(pupils.id separator ",")                                                                         
|       1 | 2292   

But, if i get the associated pupil ids in a slower, step by step way, then i get different results:

select group_concat(user_group_teachers.user_group_id separator ",") 
from user_group_teachers 
where user_group_teachers.teacher_id = 1 
group by user_group_teachers.teacher_id;

I get

| group_concat(user_group_teachers.user_group_id separator ",")  
| 12,1033,2117,2280,2281   

Plugging these values (user_group ids) into another query:

select group_concat(user_group_members.pupil_id separator ",") 
from user_group_members 
where user_group_members.user_group_id in (12,1033,2117,2280,2281) 
group by user_group_members.user_group_id;

I get

| group_concat(user_group_members.pupil_id separator ",") 
| 47106,47107  

Thanks for the group_concat method btw, that's handy :)

Answer 1

This is what i came up with:

    select pupil_group_teachers.teacher_id, group_concat(pupil_group_members.pupil_id separator ',') 
    from pupil_group_teachers join pupil_groups on pupil_group_teachers.pupil_group_id = pupil_groups.id
    join pupil_group_members on pupil_group_members.pupil_group_id = pupil_groups.id
    group by pupil_group_teachers.teacher_id;

it seems to work, and is really fast. Lyhan (who has since deleted his answer) and David Fleeman both helped me figure it out. Cheers guys.

Answer 2

I made a couple comments above that are important to the solution for this, but I think you could start with these two queries to see if it gets you far enough along to get what you need.

To get ordered lists for a teacher for pupils across all groups, you could do this:

select distinct t.teacher_id, m.pupil_id
from user_groups g
    inner join user_group_teachers t
        on t.user_group_id = g.id
    inner join user_group_members m
        on t.user_group_id = g.id
order by t.teacher_id, m.pupil_id

To get ordered lists for a teacher for pupils with the relationship to group in tact, you could do this:

select g.id, t.teacher_id, m.pupil_id
from user_groups g
    inner join user_group_teachers t
        on t.user_group_id = g.id
    inner join user_group_members m
        on t.user_group_id = g.id
order by g.id, t.teacher_id, m.pupil_id

You would have to walk these result sets and transform them into the nested arrays, but it is the data you wanted.

Update: Update: If the data set is too large or you do not want to walk a single result set, then you could do this to emulate the results of the first query above and build your sub-arrays based on query result sets:

/* Use this query to drive the batch */
select distinct t.teacher_id
from user_groups_teachers t
order by t.teacher_id

/* Inside a loop based on first query result, pull out the array of pupils for a teacher */
select distinct m.pupil_id
from user_groups_members m
    inner join user_groups g
        on g.id = m.user_group_id
    inner join user_groups_teachers t
        on t.user_group_id = g.id
where t.teacher_id = /* parameter */
order by m.pupil_id