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