How to get collection users with collection tasks inside?

Question

User has_many tasks Task belongs_to user (Task has key user_id)

I`m trying make sql query

User.find_by_sql("
  SELECT 
    users.id, 
    users.name, 
    tasks # <-- how can I get collection tasks inside each users?
  FROM users
  JOIN tasks
    ON tasks.user_id = users.id
")

I would like to get next response

[
  {
    id: 1,
    name: Jon,
    tasks: [ # <- collection, please
      { id: 1, user_id: 1, title: ... },
      { id: 2, user_id: 1, title: ... }
    ]
  },
  {
    id: 2,
    name: Sofia,
    tasks: [ # <- collection, please
      { id: 3, user_id: 2, title: ... },
      { id: 4, user_id: 2, title: ... }
    ]
]

If use ActiveRecord, it be User.includes(:tasks)

How can I get collection tasks inside each users? (sql) Is it possible?

max max · Accepted Answer · 2019-11-19T22:14:26

To select rows from the joined table just specify the name of the table and the column:

SELECT 
  users.id,
  users.name,
  tasks.id AS task_id, 
  tasks.name AS task_name
FROM 
  users
LEFT OUTER JOIN 
  tasks ON tasks.user_id = users.id

Since SQL is tabular the result is a list of rows and columns:

 id |   name   | task_id | task_name 
----+----------+---------+-----------
  2 | Xzbdulia |       1 | Cleaning
  2 | Xzbdulia |       2 | Laundry
  2 | Xzbdulia |       3 | Coding
  3 | Elly     |       4 | Cleaning
  3 | Elly     |       5 | Laundry
  3 | Elly     |       6 | Coding
  4 | Lewis    |       7 | Cleaning
  4 | Lewis    |       8 | Laundry
  4 | Lewis    |       9 | Coding
  5 | Hang     |      10 | Cleaning
  5 | Hang     |      11 | Laundry
  5 | Hang     |      12 | Coding

If you really want to rough it and do this as a raw sql query and handle processing the results yourself you can do:

sql = <<~SQL
  SELECT
    users.id,
    users.name,
    tasks.id AS task_id,
    tasks.name AS task_name
  FROM
    users
  LEFT OUTER JOIN
    tasks ON tasks.user_id = users.id;
SQL
results = User.connection.execute(sql).each_with_object({}) do |row, memo|
  id = row["id"]
  memo[id] ||= {
    id: id,
    name: row["name"],
    tasks: []
  }
  memo[id][:tasks].push({
    id: row["task_id"],
    name: row["task_name"]
  }) unless row["task_id"].nil?
end.values

[{:id=>2, :name=>"Xzbdulia", :tasks=>[{:id=>1, :name=>"Cleaning"}, {:id=>2, :name=>"Laundry"}, {:id=>3, :name=>"Coding"}]}, {:id=>3, :name=>"Elly", :tasks=>[{:id=>4, :name=>"Cleaning"}, {:id=>5, :name=>"Laundry"}, {:id=>6, :name=>"Coding"}]}, {:id=>4, :name=>"Lewis", :tasks=>[{:id=>7, :name=>"Cleaning"}, {:id=>8, :name=>"Laundry"}, {:id=>9, :name=>"Coding"}]}, {:id=>5, :name=>"Hang", :tasks=>[{:id=>10, :name=>"Cleaning"}, {:id=>11, :name=>"Laundry"}, {:id=>12, :name=>"Coding"}]}]

But whats the point when you can just use ActiveRecord and ActiveModel::Serializers::JSON and do this in two lines:

@users = User.includes(:tasks).select(:id, :name)
             .as_json(only: [:id, :name], include: { tasks: { only: [:id, :name]} })

Yeah it does two queries ands selects a few more columns on tasks but I doubt there will be any really noticeable performance difference plus its a lot more maintainable. There are better things to spend your time on like tests.

How to get collection users with collection tasks inside?

1 Answers