Rails joins & includes &

又是一个起源于群友(154112964 rails群 欢迎使用rails的所有人)讨论的玩意

先放一些模型&关联在前面充当整个背景

class Author < ActiveRecord::Base 
  has_many :posts
end

class Post < ActiveRecord::Base  
  belongs_to :author  
  has_many :comments  
  has_many :tags
  has_many :guests, through: :comments
end 

class Comment < ActiveRecord::Base
  belongs_to :post
  has_one :guest
end 

class Guest < ActiveRecord::Base
  belongs_to :comment
end 

class Tag < ActiveRecord::Base
  belongs_to :post
end

Joins

• rails 中 joins 产生的均是 inner join (取交集)
• joins接收者可以是model类本身 也可以是 ActiveRecord::Relation 的实例

belongs_to

 Post.joins(:author)  #返回所有 有作者的 Post  INNER JOIN

SELECT "posts".* FROM "posts" 
  INNER JOIN "authors" ON "authors"."id" = "posts"."author_id"

has_many

author = Author.create
Author.joins(:posts)
Author.joins(:posts).where(posts: { author: author})

产生的sql分别如下 － 可以看出Rails在关联上的处理还是非常机智的

SELECT "authors".* FROM "authors" 
  INNER JOIN "posts" ON "posts"."author_id" = "authors"."id"

SELECT "authors".* FROM "authors" 
  INNER JOIN "posts" ON "posts"."author_id" = "authors"."id" 
  WHERE "posts"."author_id" = 1

先撸到这里 。老夫去看雪了。

• joins也可以连接多个关联

# 下面两个操作结果等同
Post.joins(:author, :comments)
Post.joins(:author).joins(:comments)

SELECT "posts".* FROM "posts"
 INNER JOIN "authors" ON "authors"."id" = "posts"."author_id"
 INNER JOIN "comments" ON "comments"."post_id" = "posts"."id"

这样的链式操作会导致很大的重复产生。inner join造成的 不介意可以uniq

嵌套连接 依旧是 链式操作

# comment has_one guest
Post.joins(comments: :guest)

SELECT "posts".* FROM "posts"
 INNER JOIN "comments" ON "comments"."post_id" = "posts"."id"
 INNER JOIN "guests" ON "guests"."comment_id" = "comments"."id"

# 几何级的重复
Author.joins(posts: [{ comments: :guest}, :tags])

SELECT "authors".* FROM "authors"
 INNER JOIN "posts" ON "posts"."author_id" = "authors"."id"
 INNER JOIN "comments" ON "comments"."post_id" = "posts"."id"
 INNER JOIN "guests" ON "guests"."comment_id" = "comments"."id"
 INNER JOIN "tags" ON "tags"."post_id" = "posts"."id"

includes 我单独列出来这个吧

eager_load LEFT OUTER JOIN

has_many

ords =Order.eager_load(:line_items)

SELECT "orders"."id" AS t0_r0, "orders"."customer_id" AS t0_r1, "orders"."created_at" AS t0_r2, "orders"."updated_at" AS t0_r3, 
"line_items"."id" AS t1_r0, "line_items"."order_id" AS t1_r1, "line_items"."created_at" AS t1_r2, "line_items"."updated_at" AS t1_r3 
FROM "orders" LEFT OUTER JOIN "line_items" ON "line_items"."order_id" = "orders"."id"

可以看出 已经LEFF OUTER JOIN了 line_items 下面这个遍历是不会产生SQL查询的

ords.each do |order|
  order.line_items
end

o = Order.eager_load(:line_items).where("customer_id < ?", 5)

SELECT "orders"."id" AS t0_r0, "orders"."customer_id" AS t0_r1, "orders"."created_at" AS t0_r2, "orders"."updated_at" AS t0_r3,
   "line_items"."id" AS t1_r0, "line_items"."order_id" AS t1_r1, "line_items"."created_at" AS t1_r2, "line_items"."updated_at" AS t1_r3 
   FROM "orders" LEFT OUTER JOIN "line_items" ON "line_items"."order_id" = "orders"."id" WHERE (customer_id < 5)

belongs_to

也是提前加载 和has_many 类似 不过多陈述