返回筛选后has_many关联中的每一行



拥有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' } 
])

您必须小心joinsincludes,它们根据关联返回不同的结果。

我们有"作者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

includesN+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对象中。

你同时运行joinsincludes,这结合了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查询问题)

😉

相关内容

  • 没有找到相关文章

最新更新