Return every row from has_many association after filter

user_1323718 0
Viewed 142

Having models Author and Book where Author has_many books, and a scope on the Author like:

scope :by_books,->(book_ids) { joins(:books).where(books: { id: book_ids }) }

with the intent of filtering Authors if they are authors of a given book, results only the author and the specified books only in the result of:

Author.by_books(1).includes(:books).as_json(include: :books)

Whereas I would expect to have all the books of the author in the resulting json. I can do that by using .joins(:books) instead of .includes(:books) but now I am completely confused about what joins and includes do. I wonder if somebody could give me an explanation where my expectation goes wrong.

(The code given is an analog of my actual, it might be flawed in terms of syntax but I believe the behavior is the same)

associations activerecord ruby-on-rails ruby
2 Answers
  • joins is a pure SQL word query to join 2 tables.
  • includes is for eager loading (avoid n+1 query issue)

user_26164740 
class Author < ApplicationRecord
  has_many :books
end

class Book < ApplicationRecord
  belongs_to :author
end

Author.create!([
  { name: 'Author 1',
    books: [ Book.new(name: 'Book 1 of A1'), Book.new(name: 'Book 2 of A1') ] },
  { name: 'Author 2',
    books: [ Book.new(name: 'Book 1 of A2') ] },
  { name: 'Author 3' } 
])

You have to be careful with joins and includes, they return different results depending on associations.

We have 'Author 1' who has two books, 'Author 2' who has one book, and 'Author 3' without books.

joins does an INNER JOIN and instantiates new objects based on database result, this could return duplicate records and not return records that have no association

>> Author.joins(:books)
  Author Load (1.3ms)  SELECT "authors".* FROM "authors" INNER JOIN "books" ON "books"."author_id" = "authors"."id"
=> [ #<Author:0x00007f0dd278d818 id: 1, name: "Author 1">,
     #<Author:0x00007f0dd278d688 id: 1, name: "Author 1">, # duplicate
     #<Author:0x00007f0dd278d598 id: 2, name: "Author 2">] 
   # 'Author 3' is not in the result

includes is a rails solution to N+1. It runs two queries aka preload or a single query aka eager_load

>> Author.includes(:books)
  Author Load (0.8ms)  SELECT "authors".* FROM "authors"
  Book Load (1.1ms)  SELECT "books".* FROM "books" WHERE "books"."author_id" IN ($1, $2, $3)  [["author_id", 1], ["author_id", 2], ["author_id", 3]]
=> [ #<Author:0x00007f0dd27e59c8 id: 1, name: "Author 1">, 
     #<Author:0x00007f0dd27e5900 id: 2, name: "Author 2">, 
     #<Author:0x00007f0dd27e5838 id: 3, name: "Author 3">]

When you have conditions it does an eager_load in a single query

# same as  Author.eager_load(:books)
>> Author.includes(:books).references(:books)
  SQL (1.0ms)  SELECT "authors"."id" AS t0_r0, "authors"."name" AS t0_r1, "books"."id" AS t1_r0, "books"."name" AS t1_r1, "books"."author_id" AS t1_r2 FROM "authors" LEFT OUTER JOIN "books" ON "books"."author_id" = "authors"."id"                                                          
=> [ #<Author:0x00007f0dd283a1f8 id: 1, name: "Author 1">, 
     #<Author:0x00007f0dd2839bb8 id: 2, name: "Author 2">, 
     #<Author:0x00007f0dd2839780 id: 3, name: "Author 3">]

With this you get all the authors with or without books, books objects are preloaded into author object.

>> Author.includes(:books).to_a.first.instance_variable_get('@association_cache')
  Author Load (0.7ms)  SELECT "authors".* FROM "authors"
  Book Load (0.7ms)  SELECT "books".* FROM "books" WHERE "books"."author_id" IN ($1, $2, $3)  [["author_id", 1], ["author_id", 2], ["author_id", 3]]
=> {:books=>
  #<ActiveRecord::Associations::HasManyAssociation:0x00007f0dd28ff9d0
   @association_ids=nil,
   @association_scope=nil,
   @disable_joins=false,
   @loaded=true,
   @owner=#<Author:0x00007f0dd28f83b0 id: 1, name: "Author 1">,
   @reflection=#<ActiveRecord::Reflection::HasManyReflection:0x00007f0dd58a3f38 ... >,
   @replaced_or_added_targets=#<Set: {}>,
   @stale_state=nil,
   @target=[ #<Book:0x00007f0dd28fc230 id: 1, name: "Book 1 of A1", author_id: 1>, 
             #<Book:0x00007f0dd28fc078 id: 2, name: "Book 2 of A1", author_id: 1>]>}
  #          ^
  # books are preloaded

>> Author.includes(:books).to_a.last.instance_variable_get('@association_cache')
  Author Load (0.9ms)  SELECT "authors".* FROM "authors"
  Book Load (0.7ms)  SELECT "books".* FROM "books" WHERE "books"."author_id" IN ($1, $2, $3)  [["author_id", 1], ["author_id", 2], ["author_id", 3]]
=> {:books=>
  #<ActiveRecord::Associations::HasManyAssociation:0x00007f0dd2a017c0
   ...,
   @target=[]>}
  #        ^
  # doesn't have any books

When includes does an eager_load it runs LEFT OUTER JOIN so you get all the authors and rails combines duplicated results, unlike the joins method.

>> ActiveRecord::Base.connection.execute(Author.eager_load(:books).to_sql).to_a
   (0.8ms)  SELECT "authors"."id" AS t0_r0, "authors"."name" AS t0_r1, "books"."id" AS t1_r0, "books"."name" AS t1_r1, "books"."author_id" AS t1_r2 FROM "authors" LEFT OUTER JOIN "books" ON "books"."author_id" = "authors"."id"                                                                                                                                                          
=>
[{"t0_r0"=>1, "t0_r1"=>"Author 1", "t1_r0"=>1, "t1_r1"=>"Book 1 of A1", "t1_r2"=>1},
 {"t0_r0"=>1, "t0_r1"=>"Author 1", "t1_r0"=>2, "t1_r1"=>"Book 2 of A1", "t1_r2"=>1}, 
# ^ duplicate `Author 1` like the `joins` method
 {"t0_r0"=>2, "t0_r1"=>"Author 2", "t1_r0"=>3, "t1_r1"=>"Book 1 of A2", "t1_r2"=>2},
 {"t0_r0"=>3, "t0_r1"=>"Author 3", "t1_r0"=>nil, "t1_r1"=>nil, "t1_r2"=>nil}]

'Author 1' results get merged into one Author object.

You're running joins and includes at the same time which combined does an eager_load style query.

Author.joins(:books).includes(:books).where(books: {id: [1,2]})
 # INNER JOIN 
 # 2 database results
 # 1 Author object 
 # Books are preloaded

Author.joins(:books).where(books: {id: [1,2]})
 # INNER JOIN
 # 2 database results
 # 2 Author objects
 # No books are loaded

Author.includes(:books).where(books: {id: [1,2]})
 # LEFT OUTER JOIN
 # 2 database results
 # 1 Author object 
 # Books are preloaded
user_2070900
Related