高效的 Join 查询 - 可以使用 ActiveRecord 来完成吗

发布于 2024-12-25 20:39:39 字数 727 浏览 0 评论 0原文

我有 4 个模型，A、B、C 和 D

class A < ActiveRecord::Base
  has_many :B
  has_many :C, :through => :B
end  

class B < ActiveRecord::Base
  belongs_to :A  
  has_many   :C
  has_many   :D, :through => :C
end  

class C < ActiveRecord::Base    
  belongs_to :B
end    

class D < ActiveRecord::Base    
  belongs_to :C
end

我有一个非常幼稚的实现，非常明显......

<% A.B.each do |b| %>
  <%= b.number %>
  <% b.C.each do |c| %>
    <%= c.name %>
  <% end %>
<% end %>

获得 A 的所有 C 的最佳方法是什么？为 A 获得 All D 的最佳方式是什么？

我想使用带有“created_at”值的 order_by 子句来获取所有“C”，而不是迭代 B。

可能是我错过了一些 ActiveRecord 魔法？

我很感激任何帮助。

原文

I have 4 models, A, B, C and D

class A < ActiveRecord::Base
  has_many :B
  has_many :C, :through => :B
end  

class B < ActiveRecord::Base
  belongs_to :A  
  has_many   :C
  has_many   :D, :through => :C
end  

class C < ActiveRecord::Base    
  belongs_to :B
end    

class D < ActiveRecord::Base    
  belongs_to :C
end

I have a very naive implementation which is very obvious ...

<% A.B.each do |b| %>
  <%= b.number %>
  <% b.C.each do |c| %>
    <%= c.name %>
  <% end %>
<% end %>

What's the best way to get All C for A?
What's the best way get All D for A?

I want to get all 'C' using order_by clause with "created_at" value instead of iterating through B.

May be I'm missing some ActiveRecord magic?

I appreciate any help.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我为君王 2025-01-01 20:39:39

首先，您需要进行一些更改。

C 类 需要与 D 关联

C类< ActiveRecord::基础
  属于：B
  有一个：D
结尾

如果您想访问 A 的 D，您也需要指定这一点。

A 类 < ActiveRecord::基础
  有很多：B
  has_many :C, :through =>; :B
  has_many :D, :through =>; ：C
结尾

现在，要访问 A 的所有 C：

-> a = A.where(:id => 1).includes(:C).first
  A Load (0.2ms)  SELECT "as".* FROM "as" WHERE "as"."id" = 1 LIMIT 1
  B Load (0.1ms)  SELECT "bs".* FROM "bs" WHERE "bs"."a_id" IN (1)
  C Load (0.1ms)  SELECT "cs".* FROM "cs" WHERE "cs"."b_id" IN (1, 2)
 => #<A id: 1, created_at: "2012-01-10 04:28:42", updated_at: "2012-01-10 04:28:42"> 
-> a.C
 => [#<C id: 1, b_id: 1, created_at: "2012-01-10 04:30:10", updated_at: "2012-01-10 04:30:10">, #<C id: 2, b_id: 1, created_at: "2012-01-10 04:30:11", updated_at: "2012-01-10 04:30:11">, #<C id: 3, b_id: 2, created_at: "2012-01-10 04:30:21", updated_at: "2012-01-10 04:30:21">, #<C id: 4, b_id: 2, created_at: "2012-01-10 04:30:21", updated_at: "2012-01-10 04:30:21">]

请注意，当您调用 aC 时，另一个查询不会被执行。这是因为 ActiveRecord 知道您想要通过 include 调用访问找到的 A 的 C，并生成最小数量的查询。 D 也是如此：

-> a = A.where(:id => 1).includes(:D).first
  A Load (0.1ms)  SELECT "as".* FROM "as" WHERE "as"."id" = 1 LIMIT 1
  B Load (0.1ms)  SELECT "bs".* FROM "bs" WHERE "bs"."a_id" IN (1)
  C Load (0.1ms)  SELECT "cs".* FROM "cs" WHERE "cs"."b_id" IN (1, 2)
  D Load (0.1ms)  SELECT "ds".* FROM "ds" WHERE "ds"."c_id" IN (1, 2, 3, 4)

假设您想要所有 A 的 D，但想要 C ordered：

A.where(:id => 1).includes(:C).order('cs.created_at DESC').includes(:D)

请注意，您还可以将其设置为关联的默认值：

:order 选项指示关联对象的接收顺序（采用 SQL ORDER BY 子句使用的语法）。
类客户 < ActiveRecord::基础
  has_many :订单, :订单 => “日期_确认 DESC”
结尾

First of all, you need to make a couple changes.

class C needs an association to D

class C < ActiveRecord::Base
  belongs_to :B
  has_one :D
end

If you want to access A's D's, you need to specify this as well.

class A < ActiveRecord::Base
  has_many :B
  has_many :C, :through => :B
  has_many :D, :through => :C
end

Now, to access all of A's C's:

-> a = A.where(:id => 1).includes(:C).first
  A Load (0.2ms)  SELECT "as".* FROM "as" WHERE "as"."id" = 1 LIMIT 1
  B Load (0.1ms)  SELECT "bs".* FROM "bs" WHERE "bs"."a_id" IN (1)
  C Load (0.1ms)  SELECT "cs".* FROM "cs" WHERE "cs"."b_id" IN (1, 2)
 => #<A id: 1, created_at: "2012-01-10 04:28:42", updated_at: "2012-01-10 04:28:42"> 
-> a.C
 => [#<C id: 1, b_id: 1, created_at: "2012-01-10 04:30:10", updated_at: "2012-01-10 04:30:10">, #<C id: 2, b_id: 1, created_at: "2012-01-10 04:30:11", updated_at: "2012-01-10 04:30:11">, #<C id: 3, b_id: 2, created_at: "2012-01-10 04:30:21", updated_at: "2012-01-10 04:30:21">, #<C id: 4, b_id: 2, created_at: "2012-01-10 04:30:21", updated_at: "2012-01-10 04:30:21">]

Notice how another query is not executed when you call a.C. This is because ActiveRecord knows you will want to access the found A's C's by the include call, and generates the minimum number of queries. Same goes for D's:

-> a = A.where(:id => 1).includes(:D).first
  A Load (0.1ms)  SELECT "as".* FROM "as" WHERE "as"."id" = 1 LIMIT 1
  B Load (0.1ms)  SELECT "bs".* FROM "bs" WHERE "bs"."a_id" IN (1)
  C Load (0.1ms)  SELECT "cs".* FROM "cs" WHERE "cs"."b_id" IN (1, 2)
  D Load (0.1ms)  SELECT "ds".* FROM "ds" WHERE "ds"."c_id" IN (1, 2, 3, 4)

Say you wanted all A's D's but wanted C's ordered:

A.where(:id => 1).includes(:C).order('cs.created_at DESC').includes(:D)

Note you can also set this as a default on the association:

The :order option dictates the order in which associated objects will be received (in the syntax used by an SQL ORDER BY clause).
class Customer < ActiveRecord::Base
  has_many :orders, :order => "date_confirmed DESC"
end

回复收藏 0 原文

~没有更多了~