Rails joins & includes & eager_load

又是一个起源于群友(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 类似 不过多陈述

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容

  • Rails里面includes是比较常用的 依旧先码出背景 关联 includes方法指定使用关联时要按需加载的间...
    我天真无邪阅读 3,077评论 0 2
  • Joins, reporting queries, and subselects 在抓取策略这篇文章中有提到dyn...
    ilaoke阅读 3,570评论 0 3
  • 1.caching with instance variables2.dynamic find_by method...
    Jayzen阅读 1,422评论 0 1
  • 一、心得体会1、今天完成了什么? Rails guide 4 170页 5个小时 重看了镐头书看了第一部分 1个小...
    柳辉阅读 367评论 0 1
  • 最近一直忙着写SQLAlchemy的教程,但是在看到关系查询时,需要引入join的概念。也只怪的当初数据库没有学好...
    治部少辅阅读 17,048评论 6 37