12/08/2018, 16:47

Joins, includes, preload, and eager load in Rails

Rails cung cấp bốn cách khác nhau để load association data 1 preload: Luôn thực thi 2 câu lệnh riêng biệt. Blog.preload(:posts) Blog Load (3.2ms) SELECT "blogs".* FROM "blogs" Post Load (1.2ms) SELECT "posts".* FROM "posts" WHERE "posts"."blog_id" IN (1, 2, 3) eager_load Chỉ thực hiện 1 ...

Rails cung cấp bốn cách khác nhau để load association data 1 preload: Luôn thực thi 2 câu lệnh riêng biệt.

Blog.preload(:posts)
  Blog Load (3.2ms)  SELECT "blogs".* FROM "blogs"
  Post Load (1.2ms)  SELECT "posts".* FROM "posts" WHERE "posts"."blog_id" IN (1, 2, 3)
  1. eager_load Chỉ thực hiện 1 query LEFT OUTER JOIN Join trước, và thực hiện where sau. Hoạt dộng giống như includes + references.

Blog.eager_load(:posts) SQL (0.4ms) SELECT "blogs"."id" AS t0_r0, "blogs"."name" AS t0_r1, "blogs"."author" AS t0_r2, "blogs"."created_at" AS t0_r3, "blogs"."updated_at" AS t0_r4, "posts"."id" AS t1_r0, "posts"."title" AS t1_r1, "posts"."created_at" AS t1_r2, "posts"."updated_at" AS t1_r3, "posts"."blog_id" AS t1_r4 FROM "blogs" LEFT OUTER JOIN "posts" ON "posts"."blog_id" = "blogs"."id"

Blog.eager_load(:posts).where(name: 'Blog 1') SQL (0.4ms) SELECT "blogs"."id" AS t0_r0, "blogs"."name" AS t0_r1, "blogs"."author" AS t0_r2, "blogs"."created_at" AS t0_r3, "blogs"."updated_at" AS t0_r4, "posts"."id" AS t1_r0, "posts"."title" AS t1_r1, "posts"."created_at" AS t1_r2, "posts"."updated_at" AS t1_r3, "posts"."blog_id" AS t1_r4 FROM "blogs" LEFT OUTER JOIN "posts" ON "posts"."blog_id" = "blogs"."id" WHERE "blogs"."name" = ? [["name", "Blog 1"]]

Blog.eager_load(:posts).where(name: 'Blog 1').where(posts: {title: 'Post 1-1'}) SQL (0.4ms) SELECT "blogs"."id" AS t0_r0, "blogs"."name" AS t0_r1, "blogs"."author" AS t0_r2, "blogs"."created_at" AS t0_r3, "blogs"."updated_at" AS t0_r4, "posts"."id" AS t1_r0, "posts"."title" AS t1_r1, "posts"."created_at" AS t1_r2, "posts"."updated_at" AS t1_r3, "posts"."blog_id" AS t1_r4 FROM "blogs" LEFT OUTER JOIN "posts" ON "posts"."blog_id" = "blogs"."id" WHERE "blogs"."name" = ? AND "posts"."title" = ? [["name", "Blog 1"], ["title", "Post 1-1"]]

  1. includes Hoạt động dựa trên tình huống: Giống như preload:
Blog.includes(:posts)
  Blog Load (2.8ms)  SELECT "blogs".* FROM "blogs"
  Post Load (0.7ms)  SELECT "posts".* FROM "posts" WHERE "posts"."blog_id" IN (1, 2, 3)
Blog.includes(:posts).where(name: 'Blog 1')
  Blog Load (0.7ms)  SELECT "blogs".* FROM "blogs" WHERE "blogs"."name" = ?  [["name", "Blog 1"]]
  Post Load (0.3ms)  SELECT "posts".* FROM "posts" WHERE "posts"."blog_id" IN (1)

Hoạt đống giống preload:

Blog.includes(:posts).where(name: 'Blog 1').where(posts: {title: 'Post 1-1'})
  SQL (0.2ms)  SELECT "blogs"."id" AS t0_r0, "blogs"."name" AS t0_r1, "blogs"."author" AS t0_r2, "blogs"."created_at" AS t0_r3, "blogs"."updated_at" AS t0_r4, "posts"."id" AS t1_r0, "posts"."title" AS t1_r1, "posts"."created_at" AS t1_r2, "posts"."updated_at" AS t1_r3, "posts"."blog_id" AS t1_r4 FROM "blogs" LEFT OUTER JOIN "posts" ON "posts"."blog_id" = "blogs"."id" WHERE "blogs"."name" = ? AND "posts"."title" = ?  [["name", "Blog 1"], ["title", "Post 1-1"]]
  1. joins
Blog.joins(:posts)
  Blog Load (0.2ms)  SELECT "blogs".* FROM "blogs" INNER JOIN "posts" ON "posts"."blog_id" = "blogs"."id"
 Blog.joins(:posts).count
   (0.3ms)  SELECT COUNT(*) FROM "blogs" INNER JOIN "posts" ON "posts"."blog_id" = "blogs"."id"
 => 15
Blog.eager_load(:posts).count
   (0.4ms)  SELECT COUNT(DISTINCT "blogs"."id") FROM "blogs" LEFT OUTER JOIN "posts" ON "posts"."blog_id" = "blogs"."id"
 => 3
Blog.joins(:posts).each do |blog|
>   puts blog.posts.map(&:title).join(', ')
> end
  Blog Load (0.2ms)  SELECT "blogs".* FROM "blogs" INNER JOIN "posts" ON "posts"."blog_id" = "blogs"."id"
  Post Load (0.3ms)  SELECT "posts".* FROM "posts" WHERE "posts"."blog_id" = ?  [["blog_id", 1]]
Post 1-1, Post 1-2, Post 1-3, Post 1-4, Post 1-5
  Post Load (0.1ms)  SELECT "posts".* FROM "posts" WHERE "posts"."blog_id" = ?  [["blog_id", 1]]
Post 1-1, Post 1-2, Post 1-3, Post 1-4, Post 1-5
  Post Load (0.1ms)  SELECT "posts".* FROM "posts" WHERE "posts"."blog_id" = ?  [["blog_id", 1]]
Post 1-1, Post 1-2, Post 1-3, Post 1-4, Post 1-5
  Post Load (0.1ms)  SELECT "posts".* FROM "posts" WHERE "posts"."blog_id" = ?  [["blog_id", 1]]
Post 1-1, Post 1-2, Post 1-3, Post 1-4, Post 1-5
  Post Load (0.1ms)  SELECT "posts".* FROM "posts" WHERE "posts"."blog_id" = ?  [["blog_id", 1]]
Post 1-1, Post 1-2, Post 1-3, Post 1-4, Post 1-5
  Post Load (0.1ms)  SELECT "posts".* FROM "posts" WHERE "posts"."blog_id" = ?  [["blog_id", 2]]
Post 2-1, Post 2-2, Post 2-3, Post 2-4, Post 2-5
  Post Load (0.1ms)  SELECT "posts".* FROM "posts" WHERE "posts"."blog_id" = ?  [["blog_id", 2]]
Post 2-1, Post 2-2, Post 2-3, Post 2-4, Post 2-5
  Post Load (0.1ms)  SELECT "posts".* FROM "posts" WHERE "posts"."blog_id" = ?  [["blog_id", 2]]
Post 2-1, Post 2-2, Post 2-3, Post 2-4, Post 2-5
  Post Load (0.1ms)  SELECT "posts".* FROM "posts" WHERE "posts"."blog_id" = ?  [["blog_id", 2]]
Post 2-1, Post 2-2, Post 2-3, Post 2-4, Post 2-5
  Post Load (0.1ms)  SELECT "posts".* FROM "posts" WHERE "posts"."blog_id" = ?  [["blog_id", 2]]
Post 2-1, Post 2-2, Post 2-3, Post 2-4, Post 2-5
  Post Load (0.1ms)  SELECT "posts".* FROM "posts" WHERE "posts"."blog_id" = ?  [["blog_id", 3]]
Post 3-1, Post 3-2, Post 3-3, Post 3-4, Post 3-5
  Post Load (0.2ms)  SELECT "posts".* FROM "posts" WHERE "posts"."blog_id" = ?  [["blog_id", 3]]
Post 3-1, Post 3-2, Post 3-3, Post 3-4, Post 3-5
  Post Load (0.1ms)  SELECT "posts".* FROM "posts" WHERE "posts"."blog_id" = ?  [["blog_id", 3]]
Post 3-1, Post 3-2, Post 3-3, Post 3-4, Post 3-5
  Post Load (0.1ms)  SELECT "posts".* FROM "posts" WHERE "posts"."blog_id" = ?  [["blog_id", 3]]
Post 3-1, Post 3-2, Post 3-3, Post 3-4, Post 3-5
  Post Load (0.1ms)  SELECT "posts".* FROM "posts" WHERE "posts"."blog_id" = ?  [["blog_id", 3]]
Post 3-1, Post 3-2, Post 3-3, Post 3-4, Post 3-5
0