1

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.

Ahmad hamza
  • 1,816
  • 1
  • 23
  • 46
  • 2
    why don't you want to want to use `includes`? – SomeSchmo Apr 28 '17 at 21:58
  • Because `includes` takes more processing time when there is a huge data. You must know that `includes` brings up unnecessary data as well. Also recommended in the rails guides to use `joins` over `includes` – Ahmad hamza Apr 29 '17 at 11:40

1 Answers1

0

.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"?

Community
  • 1
  • 1
max
  • 96,212
  • 14
  • 104
  • 165
  • I'm getting `ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR: column user_groups.task_id does not exist` – Ahmad hamza Apr 28 '17 at 22:03
  • Its a `has_and_belongs_to_many` association which has a third join table called as `tasks_user_groups` with columns `analysis_task_id`, `user_group_id` – Ahmad hamza Apr 28 '17 at 22:04
  • I was just guessing what your schema is. You can get the correct join string by running `Task.joins(:user_groups)` in the console and replacing the INNER keyword with OUTER. – max Apr 28 '17 at 22:06