I have two models
class Task < AR::Base
habtm :user_groups
end
class UserGroup < AR::Base
habtm :tasks
end
Requirement:
t = Task.where(id: 45734)
t.user_group_ids #[54, 523, 78]
The above line will cause 2 queries.
I want to do this in one query without using includes.
My Attempt:
tasks = Task.joins(:user_groups).select('tasks.id, user_groups.id as ug_id').where(customer_id: 5)
But it is retrieving only single user group even for the tasks which has multiple groups associated.
Update
As Max suggested:
t = Task.joins('LEFT OUTER JOIN tasks_user_groups ON tasks_user_groups . analysis_task_id = tasks . id INNER JOIN user_groups ON user_groups . id = tasks_user_groups . user_group_id').select('user_groups.id').where(id: 36299)
Now i'm getting two tasks which has the same id but two different user groups.
Please suggest how to achieve it.
.joins uses an INNER LEFT JOIN so it will only give the rows from tasks that have a match in users_groups.
.includes gets around this by using two separate queries.
What you want is instead is an outer join.
# Rails 5
Task.left_outer_joins(:user_groups)
# Rails 4
tasks = Task.joins('LEFT OUTER JOIN user_groups ON user_groups.task_id = tasks.id')
See:
What is the difference between "INNER JOIN" and "OUTER JOIN"?