拥有Author和Book模型,其中Author有许多本书,以及Author的作用域,如:
scope :by_books,->(book_ids) { joins(:books).where(books: { id: book_ids }) }
的目的是过滤作者,如果他们是给定书籍的作者,则只在
的结果中得到作者和指定的书籍:Author.by_books(1).includes(:books).as_json(include: :books)
而我希望在结果json中包含作者的所有书籍。我可以用.joins(:books)
来代替.includes(:books)
,但现在我完全搞不懂连接和包含是怎么做的。我想知道有没有人能给我一个解释,我的期望哪里错了。
(给出的代码是我实际的模拟,它可能在语法方面有缺陷,但我相信行为是相同的)
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' }
])
您必须小心joins
和includes
,它们根据关联返回不同的结果。
我们有"作者1"有两本书,"作者2"有一本书,"作者3"没有书。
joins
执行INNER JOIN并根据数据库结果实例化新对象,这可能返回重复的记录,而不是返回没有关联的记录
>> 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
是N+1
的rails解决方案。它运行两个查询(即preload
)或一个查询(即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">]
当你有条件时,它在一个查询中执行一个eager_load
# 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">]
这样你就得到了所有有或没有书的作者,书对象被预加载到author对象中。
>> 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
当include执行eager_load
时,它运行LEFT OUTER JOIN,因此您可以获得所有作者和rails组合重复结果,不像joins
方法。
>> 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'的结果将合并到一个Author
对象中。
你同时运行joins
和includes
,这结合了eager_load
风格的查询。
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
joins
是一个纯SQL word查询,用于连接两个表。includes
用于快速加载(避免n+1
查询问题)
😉