i've been working to try and getting a clean HABTM query for MySQL
i have the following tables
id: 1,name: test
user_id: 1,group_id: 1
user_id: 1,group_id: 2
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'
)
)
)
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