Creating a clean HABTM query

Question

i've been working to try and getting a clean HABTM query for MySQL

i have the following tables

auth_users

id: 1,name: test

auth_groups_users

user_id: 1,group_id: 1

user_id: 1,group_id: 2

auth_groups

id: 1,name: default_group

id: 2,name: another_group

I am pulling a user and inside the results i would like to get the group(s) as well.

The question is how do i join it properly... do i perform a left join... or regular.. do i join the link table first and then with what conditions? then what is next?

Thanks, please let me know if you need more info.

my start...

"SELECT * FROM auth_users AS User LEFT JOIN auth_groups_users ON (auth_groups_users.user_id = User.id) LEFT JOIN auth_groups AS Group ON (Group.id = auth_groups_users.group_id) WHERE User.id='1';"

is this correct/proper?

then i also want to group the groups into it's own result within the user.. not multiple results for the same user...

so i would like my result to be:

 array(
     'id' => 1,
     'name' => 'test',
     'Groups' => array(
           0 => array(
                'id' => 1,
                'name' => 'default_group'
           ),
           1 => array(
                'id' => 2,
                'name' => 'another_group'
           )
      )
 )

Kei Kei · Accepted Answer · 2014-01-18T15:43:43

Forget about HABTM, those academic things are a sort of garbage in real life application, though you might require to answer this question in the future (or maybe right now).

So let me clarify a bit, you have these tables

users    group_users    groups
-----    -----------    ------
id       user_id        id
name     group_id       name

So basically what you want to do is to SELECT users and have their groups listed with them, am I right ?

So the right query would be

SELECT users.name, groups.name
FROM users
INNER JOIN groups_users ON users.id = group_users.user_id
INNER JOIN groups ON groups.id = group_users.group_id

This however will list ONLY the users who have a group, if you want to select also those who haven't, you should perform a left join instead of a inner join (the first one).

I can't think of a really straight way to obtain what you want since a database will always return a set of rows. You have two ways to achieve what you want

Use MySQl's GROUP_CONCAT
Implement your own way through PHP (which isn't a big deal)

Creating a clean HABTM query

auth_users

auth_groups_users

auth_groups

1 Answers