joins

joins的使用场景应是：过滤，并且你不打算获取所关联对象的属性。如下例子，获取全部Derek有comment过的post，但并不读取comment的属性

Post.joins(:comments).where(:comments => {author: 'Derek'}).map { |post| post.title }
  Post Load (1.2ms)  SELECT  "posts".* FROM "posts" INNER JOIN "comments" ON "comments"."post_id" = "posts"."id" WHERE "comments"."author" = $1
=> ["One weird trick to better Rails apps",
 "1,234 weird tricks to faster Rails apps",
 "You wouldn't believe what happened to this Rails developer after 14 days"]


它能避免N+1问题吗？

不能，因为它并不加载所关联对象的属性到内存中，这时如果访问关联对象的属性，就会再发起查询，如下

Post.joins(:comments).where(:comments => {author: 'Derek'}).map { |post| post.comments.size }
  Post Load (1.2ms)  SELECT  "posts".* FROM "posts" INNER JOIN "comments" ON "comments"."post_id" = "posts"."id" WHERE "comments"."author" = $1
   (1.0ms)  SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = $1
   (3.0ms)  SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = $1
   (0.3ms)  SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = $1
   (1.0ms)  SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = $1
   (2.1ms)  SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = $1
   (1.4ms)  SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = $1
=> [3,5,2,4,2,1]


joins能否与includes、preload、eager_load一起使用？

能。joins的连接方法（默认是INNER JOIN）会优先于includes、eager_load。而preload不作连接

此外，与left_outer_joins一样，它返回的结果集是不会归并到父对象的，即子表返回多少，父对象就有多少个（对于left_outer_joins，父对象至少一个）

includes

它能避免N+1问题吗？

能，它先加载父对象的所有记录，然后加载那些传给includes方法的参数所表示的关联对象

从以下例子可注意到，它只发起一条额外的查询。刚才的joins与这对比来看，则是每个post都有一次count查询

Post.includes(:comments).map { |post| post.comments.size }
  Post Load (1.2ms)  SELECT  "posts".* FROM "posts"
  Comment Load (2.0ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" IN (1, 3, 4, 5, 6)
=> [3,5,2,4,2,1]


它是否总会发起另一条查询来加载关联对象？

不是，如果你有对关联对象进行where或order，则它会用LEFT OUTER JOIN，如下

Article.includes(:comments).where(comments: { visible: true })
  SELECT "articles"."id" AS t0_r0, ... "comments"."updated_at" AS t1_r5 FROM "articles" LEFT OUTER JOIN "comments" ON "comments"."article_id" = "articles"."id" WHERE (comments.visible = 1)


（注意，对于没有任何comment的article，是不会在上面的结果集中出现的，因为条件被放在了WHERE中而不是ON中）

如何避免进一步的N+1问题？这样：

Article.includes(:comments => :user)
  Article Load (0.3ms)  SELECT "articles".* FROM "articles"
  Comment Load (0.6ms)  SELECT "comments".* FROM "comments" WHERE "comments"."article_id" IN (3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
  User Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN (4, 3, 5, 6, 2)


preload/eager_load与includes的关系

preload是发起两条查询的，而eager_load则是用LEFT OUTER JOIN（因此可过滤“主表”和关联表的数据）

includes会在有where和order、references时转用eager_load，否则效果如同preload

preload不能在where中带有所关联表的过滤条件，因为这个条件会被拼到第一条语句（查父表）上，但这个语句并没连接子表

eager_load的LEFT OUTER JOIN会产生多余的父对象数据，虽然最后会归并到一个父对象上

带条件的preload

如果想查出所有user的同时，预加载各user的like_count > 100的post，可这样

# in class User
has_many :popular_posts, -> {where "like_count > 100"}, class_name: 'Post'

User.preload(:popular_posts)
#select "users".* from "users"
#select "posts".* from "posts" where "posts"."like_count" > 100 AND "posts"."user_id" in (1, 2, 3)


不过，这样条件就写死了

如果想动态设置，可试试类似如下

class Article < ApplicationRecord
  has_many :comments

  def self.preload_comments_by user
    accessor = define_preload_comments_by_if_not_yet user
    preload accessor
  end

  def self.define_preload_comments_by_if_not_yet user
    accessor_str = "comments_by_#{user.id}"
    accessor_sym = accessor_str.to_sym
    unless _reflections.has_key? accessor_str
      (@lock ||= Mutex.new).synchronize do
        unless _reflections.has_key? accessor_str
          puts accessor_str
          has_many accessor_sym, -> { where user: user }, class_name: Comment
        end
      end
    end
    accessor_sym
  end
end


使用方法

irb(main):008:0> current_user = User.find(3) # current_user可用before_action查出
  User Load (0.2ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 3], ["LIMIT", 1]]
irb(main):009:0> Article.preload_comments_by(current_user).page(2).per(5)
  Article Load (0.2ms)  SELECT  "articles".* FROM "articles" LIMIT ? OFFSET ?  [["LIMIT", 5], ["OFFSET", 5]]
  Comment Load (0.3ms)  SELECT "comments".* FROM "comments" WHERE "comments"."user_id" = 3 AND "comments"."article_id" IN (8, 9, 10, 11, 12)


references

需与includes一起使用

如果includes带的where是含有关联表过滤条件的字符串形式，需用references指定查的是那个表，这时它实际用的是eager_load。（不过，references的参数好像可以随便指定表名，甚至随便什么名字，都可以……）

总结

简单关联、过滤，用joins

需要预加载，用includes

不想用两条查询来预加载，指定eager_load

到底应该用preload作两条查询还是一条eager_load作LEFT OUTER JOIN，需看实际情况，而includes在不涉及子表条件时，倾向于使用preload