CODEWITHSUNDEEP
ruby-on-railsactiverecord
mackshkatz
mackshkatz

Reputation: 881

Confusion around Rails #joins combined with #includes

This is really nothing more than just looking for some explanation around why the joins clause is necessary for what I am doing. Both return everything I want properly, but the queries are different and one is less optimal:

TL;DR: What is Rails doing exactly when I combine the joins with the includes, I wouldn't have thought I needed the joins really. Thanks for anyone willing to actually read through this monster.

Note: object is a ToDoListsUser object, and this is all happening in an exporter for what it's worth. And ProgressItem is a joins table between User and ToDoItem.

With the joins clause (queries look good and as expected):

object.to_do_list.progress_items.where(user_id: object.user_id)
                                .joins(:to_do_item)
                                .includes(:to_do_item)

Server output:

ToDoListsUser Load (0.5ms)  SELECT "to_do_lists_users".* FROM "to_do_lists_users" WHERE "to_do_lists_users"."user_id" = $1  ORDER BY "to_do_lists_users"."id" DESC OFFSET 0  [["user_id", 543]]
ToDoList Load (0.3ms)  SELECT  "to_do_lists".* FROM "to_do_lists" WHERE "to_do_lists"."id" = $1 LIMIT 1  [["id", 144]]

SQL (1.6ms) SELECT "progress_items"."id" AS t0_r0, "progress_items"."completed" AS t0_r1, "progress_items"."user_id" AS t0_r2, "progress_items"."to_do_item_id" AS t0_r3, "progress_items"."created_at" AS t0_r4, "progress_items"."updated_at" AS t0_r5, "progress_items"."hidden" AS t0_r6, "to_do_items_progress_items"."id" AS t1_r0, "to_do_items_progress_items"."to_do_list_id" AS t1_r1, "to_do_items_progress_items"."description" AS t1_r2, "to_do_items_progress_items"."date_due" AS t1_r3, "to_do_items_progress_items"."created_at" AS t1_r4, "to_do_items_progress_items"."updated_at" AS t1_r5, "to_do_items_progress_items"."name" AS t1_r6, "to_do_items_progress_items"."element_id" AS t1_r7, "to_do_items_progress_items"."linkable_id" AS t1_r8, "to_do_items_progress_items"."linkable_type" AS t1_r9, "to_do_items_progress_items"."action" AS t1_r10 FROM "progress_items" INNER JOIN "to_do_items" "to_do_items_progress_items" ON "to_do_items_progress_items"."id" = "progress_items"."to_do_item_id" INNER JOIN "to_do_items" ON "progress_items"."to_do_item_id" = "to_do_items"."id" WHERE "to_do_items"."to_do_list_id" = $1 AND "progress_items"."user_id" = $2 ORDER BY to_do_items.created_at [["to_do_list_id", 144], ["user_id", 543]] 

ToDoList Load (0.4ms)  SELECT  "to_do_lists".* FROM "to_do_lists" WHERE "to_do_lists"."id" = $1 LIMIT 1  [["id", 133]]

SQL (3.3ms) SELECT "progress_items"."id" AS t0_r0, "progress_items"."completed" AS t0_r1, "progress_items"."user_id" AS t0_r2, "progress_items"."to_do_item_id" AS t0_r3, "progress_items"."created_at" AS t0_r4, "progress_items"."updated_at" AS t0_r5, "progress_items"."hidden" AS t0_r6, "to_do_items_progress_items"."id" AS t1_r0, "to_do_items_progress_items"."to_do_list_id" AS t1_r1, "to_do_items_progress_items"."description" AS t1_r2, "to_do_items_progress_items"."date_due" AS t1_r3, "to_do_items_progress_items"."created_at" AS t1_r4, "to_do_items_progress_items"."updated_at" AS t1_r5, "to_do_items_progress_items"."name" AS t1_r6, "to_do_items_progress_items"."element_id" AS t1_r7, "to_do_items_progress_items"."linkable_id" AS t1_r8, "to_do_items_progress_items"."linkable_type" AS t1_r9, "to_do_items_progress_items"."action" AS t1_r10 FROM "progress_items" INNER JOIN "to_do_items" "to_do_items_progress_items" ON "to_do_items_progress_items"."id" = "progress_items"."to_do_item_id" INNER JOIN "to_do_items" ON "progress_items"."to_do_item_id" = "to_do_items"."id" WHERE "to_do_items"."to_do_list_id" = $1 AND "progress_items"."user_id" = $2 ORDER BY to_do_items.created_at [["to_do_list_id", 133], ["user_id", 543]]

vs.

Without the joins clause (queries are not optimal):

object.to_do_list.progress_items.where(user_id: object.user_id)
                                .includes(:to_do_item)

Server output:

ToDoListsUser Load (0.3ms)  SELECT "to_do_lists_users".* FROM "to_do_lists_users" WHERE "to_do_lists_users"."user_id" = $1  ORDER BY "to_do_lists_users"."id" DESC OFFSET 0  [["user_id", 543]]
ToDoList Load (0.4ms)  SELECT  "to_do_lists".* FROM "to_do_lists" WHERE "to_do_lists"."id" = $1 LIMIT 1  [["id", 144]]
ProgressItem Load (2.0ms)  SELECT "progress_items".* FROM "progress_items" INNER JOIN "to_do_items" ON "progress_items"."to_do_item_id" = "to_do_items"."id" WHERE "to_do_items"."to_do_list_id" = $1 AND "progress_items"."user_id" = $2  ORDER BY to_do_items.created_at  [["to_do_list_id", 144], ["user_id", 543]]
ToDoList Load (0.5ms)  SELECT  "to_do_lists".* FROM "to_do_lists" WHERE "to_do_lists"."id" = $1 LIMIT 1  [["id", 133]]
ProgressItem Load (0.5ms)  SELECT "progress_items".* FROM "progress_items" INNER JOIN "to_do_items" ON "progress_items"."to_do_item_id" = "to_do_items"."id" WHERE "to_do_items"."to_do_list_id" = $1 AND "progress_items"."user_id" = $2  ORDER BY to_do_items.created_at  [["to_do_list_id", 133], ["user_id", 543]]
ToDoItem Load (0.4ms)  SELECT "to_do_items".* FROM "to_do_items" WHERE "to_do_items"."id" IN (193, 194, 195, 196)

Upvotes: 0

Views: 38

Answers (1)

EugZol
EugZol

Reputation: 6555

From ActiveRecord documentation:

conditions

If you want to add conditions to your included models you’ll have to explicitly reference them. For example:

User.includes(:posts).where('posts.name = ?', 'example')

Will throw an error, but this will work:

User.includes(:posts).where('posts.name = ?', 'example').references(:posts)

Note that includes works with association names while references needs the actual table name.

That happens, because actually Rails with include doesn't do join, it fetches records with two requests.

For example:

class List < ActiveRecord::Base
  has_many :tasks
end

List.includes(:tasks)
# List Load (1.9ms)  SELECT "lists".* FROM "lists"
# Task Load (0.8ms)  SELECT "tasks".* FROM "tasks" WHERE "tasks"."list_id" IN (1, 2, 3)

But with references it falls back to join:

List.includes(:tasks).references(:tasks)
# SQL (0.6ms)  SELECT "lists"."id" AS t0_r0, "lists"."name" AS t0_r1,
#              "tasks"."id" AS t1_r0, "tasks"."name" AS t1_r1 FROM
#              "lists" LEFT OUTER JOIN "tasks" ON "tasks"."list_id" = "lists"."id"

Upvotes: 1

Related Questions