Ruby on Rails进行关系查询 [英] Ruby on Rails where query with relations

问题描述

我正在尝试使用带关系的查询。

I am trying to use where query with relationships.

在这种情况下，如何使用带关系的查询？

How can I query using where with relations in this case?

这是模型

User
  has_many :projects
  has_many :reasons, through: :projects

Project
  belongs_to :user
  has_many :reasons

Reasons
  belongs_to :project

这是无效的代码

  # GET /reasons
  def index
    reasons     = current_user.reasons
    updated_at  = params[:updated_at]

    # Filter with updated_at for reloading from mobile app
    if updated_at.present?



      # This one doesn't work!!!!!!!!!!!!      
      reasons = reasons.includes(:projects).where("updated_at > ?", Time.at(updated_at.to_i))



    # Get all non deleted objects when logging in from mobile app
    else
      reasons = reasons.where(deleted: false)
    end

    render json: reasons
  end  

---更新---

感谢@AmitA，这是正确的。

This is correct thanks to @AmitA.

reasons = reasons.joins(:project).where("projects.updated_at > ?", Time.at(updated_at.to_i))

推荐答案

如果要查询项目受某些约束的所有原因，则需要使用 joins 而不是 includes ：

If you want to query all reasons whose projects have some constraints, you need to use joins instead of includes:

reasons = reasons.joins(:project).where("projects.updated_at > ?", Time.at(updated_at.to_i))

请注意，当两个都包含和联接收到一个符号，表示它们寻找关联与该确切名称。因此，您实际上不能执行 includes（：projects），但是必须执行 includes（：project）或 joins（：project）。

Note that when both includes and joins receive a symbol they look for association with that precise name. That's why you can't actually do includes(:projects), but must do includes(:project) or joins(:project).

还要注意，由其中指定的联接表上的约束必须引用表名，而不是关联名称。这就是为什么我使用 projects.updated_at （以复数形式）而不是其他任何原因的原因。换句话说，调用 where 方法时，您位于 SQL域中。

Also note that the constraints on joined tables specified by where must refer to the table name, not the association name. That's why I used projects.updated_at (in plural) rather than anything else. In other words, when calling the where method you are in "SQL domain".

包括和加入。 includes 运行一个单独的查询以加载依赖项，然后将其填充到获取的活动记录对象中。因此：

There is a difference between includes and joins. includes runs a separate query to load the dependents, and then populates them into the fetched active record objects. So:

reasons = Reason.where('id IN (1, 2, 3)').includes(:project)

将执行以下操作：

1. 运行查询 SELECT * FROM where where ID IN（1,2,3），并构造ActiveRecord对象 Reason 中的每条记录。


2. 查看获取的每个原因并提取其project_id。假设这些是11,12,13。然后运行查询 SELECT * FROM项目所在的ID为（11,12,13）并构造ActiveRecord对象 Project


3. 预填充每个原因的项目关联在第1步中获取了ActiveRecord对象。

1. Run the query SELECT * FROM reasons WHERE id IN (1,2,3), and construct the ActiveRecord objects Reason for each record.
2. Look into each reason fetched and extract its project_id. Let's say these are 11,12,13. Then run the query SELECT * FROM projects WHERE id IN (11,12,13) and construct the ActiveRecord objects Project for each record.
3. Pre-populate the project association of each Reason ActiveRecord object fetched in step 1.

上面的最后一步意味着您可以安全地进行操作了：

The last step above means you can then safely do:

reasons.first.project

将不会启动查询来获取第一个原因的项目。这就是为什么 includes 用于解决N + 1个查询的原因。但是，请注意，SQL中没有JOIN子句发生-它们是单独的SQL。因此，当您使用 includes 时，无法添加SQL约束。

And no query will be initiated to fetch the project of the first reason. This is why includes is used to solve N+1 queries. However, note that no JOIN clauses happen in the SQLs - they are separate SQLs. So you cannot add SQL constraints when you use includes.

这就是联接的地方进来。它只是联接表，以便您可以在联接的表上添加 where 约束。但是，它不会为您预先填充关联。实际上， Reason.joins（：project）永远不会实例化 Project ActiveRecord对象。

That's where joins comes in. It simply joins the tables so that you can add where constraints on the joined tables. However, it does not pre-populate the associations for you. In fact, Reason.joins(:project), will never instantiate Project ActiveRecord objects.

如果要同时执行 joins 和 includes ，则可以使用第三个称为 eager_load 的方法。您可以在此处了解更多信息。

If you want to do both joins and includes, you can use a third method called eager_load. You can read more about the differences here.

这篇关于Ruby on Rails进行关系查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！