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'
)
)
)