0

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'
           )
      )
 )
Tobias Hagenbeek
  • 1,212
  • 3
  • 15
  • 30

1 Answers1

2

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

  1. Use MySQl's GROUP_CONCAT
  2. Implement your own way through PHP (which isn't a big deal)
Jared Farrish
  • 48,585
  • 17
  • 95
  • 104
Kei
  • 771
  • 6
  • 17
  • Hye that is what i need and thanks for the explanation. I have tried tried your way, which is why i changed the post at the end. But i used your querty (with WHERE User.id = 1) and that is fine, but if my user is in multiple groups, i get multiple results (rows) back. I want to group the results for the "groups" table with the appropriate user. – Tobias Hagenbeek Jan 18 '14 at 15:47
  • So you would like to have a list of the groups and the users inside or... ? – Kei Jan 18 '14 at 15:51
  • I need one query result, where the user contains a array of all the groups belonging with that user.. – Tobias Hagenbeek Jan 18 '14 at 15:54
  • so kinda this: User = array('id' => 1,'name' => 'test','Groups' => array(0 => array('id' => 1,'name' => 'default_group'),array(1 => array('id' => 2,'name' => 'another_group'))) – Tobias Hagenbeek Jan 18 '14 at 16:01