如何在 Rails 中的 AREL 子查询上进行连接

发布于 2024-12-06 09:57:55 字数 786 浏览 0 评论 0原文

简单模型

class User
    has_many :logs


class Logs

我有一个通过外键logs.user_id 以通常方式相关的。我正在尝试使用 Arel 执行以下操作，根据 Arel 文档，它应该可以工作。

u_t = Arel::Table::new :users
l_t = Arel::Table::new :logs

counts = l_t.
    group(l_t[:user_id]).
    project(
        l_t[:user_id].as("user_id"),
        l_t[:user_id].count.as("count_all")
    )

l_t.joins(counts).on(l_t[:id].eq(counts[:user_id]))

当我这样做时，我收到错误

TypeError: Cannot visit Arel::SelectManager

但是 Arel 明确建议 Arel 可以做这种事情。

请不要写关于如何使用原始 sql、另一种类型的 Arel 查询等实现相同查询的回复。这是我感兴趣的模式，而不是该查询的具体结果。

原文

I have a simple model

class User
    has_many :logs


class Logs

related in the usual way through the foreign key logs.user_id. I'm trying to do the following using Arel and according to the Arel doc it should work.

u_t = Arel::Table::new :users
l_t = Arel::Table::new :logs

counts = l_t.
    group(l_t[:user_id]).
    project(
        l_t[:user_id].as("user_id"),
        l_t[:user_id].count.as("count_all")
    )

l_t.joins(counts).on(l_t[:id].eq(counts[:user_id]))

When I do that I get the error

TypeError: Cannot visit Arel::SelectManager

However the author of Arel explicitly suggests that Arel can do this kind of thing.

Please do not write responses on how I can achieve the same query with raw sql, another type of Arel query etc. It is the pattern I am interested in not the specific results of this query.

分享到QQ

分享到微博

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

发布评论

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

三寸金莲 2024-12-13 09:57:55

您可以使用 join_sources 从 Arel::SelectManager 实例检索 Arel::Nodes::Join ，并将其传递给 joins

使用您的示例：

l_t.joins(counts.join_sources).on(l_t[:id].eq(counts[:user_id]))

You can use join_sources to retrieve the Arel::Nodes::Join from the instance of Arel::SelectManager, and pass that to joins

Using your example:

l_t.joins(counts.join_sources).on(l_t[:id].eq(counts[:user_id]))

回复收藏 0 原文

客…行舟 2024-12-13 09:57:55

这实现了嵌套 select 子查询与 Arel 的联接：

您可以在模型文件中添加嵌套的 inner_query 和outer_query 范围并使用 ...

  inner_query = Model.inner_query(params)
  result = Model.outer_query(params).joins(Arel.sql("(#{inner_query.to_sql})"))
   .group("...")
   .order("...")

对于此变化，例如要在子查询上使用 INNER JOIN，请执行以下操作

  inner_query = Model.inner_query(params)
  result = Model.outer_query(params).joins(Arel.sql("INNER JOIN (#{inner_query.to_sql}) tablealias ON a.id = b.id"))
   .group("...")
   .order("...")

：在每个查询范围的特定连接、约束和分组中进一步修改 sql 语句，即：

scope :inner_query , -> (company_id, name) {
    select("...")
    .joins("left join table1 on table1.id = table2.id")
    .where("table1.company_id = ? and table1.name in (?)", company_id, name)
    .group("...")
}

这允许您在嵌套查询和外部查询上放置 WHERE 条件

This achieves a join of nested select subquery with Arel:

You can add the nested inner_query and an outer_query scope in your Model file and use ...

  inner_query = Model.inner_query(params)
  result = Model.outer_query(params).joins(Arel.sql("(#{inner_query.to_sql})"))
   .group("...")
   .order("...")

For variations on this, for example to use INNER JOIN on the subquery, do the following:

  inner_query = Model.inner_query(params)
  result = Model.outer_query(params).joins(Arel.sql("INNER JOIN (#{inner_query.to_sql}) tablealias ON a.id = b.id"))
   .group("...")
   .order("...")

Add in the specific joins, constraints and groupings to each of the queries' scopes to modify the sql statement further ie:

scope :inner_query , -> (company_id, name) {
    select("...")
    .joins("left join table1 on table1.id = table2.id")
    .where("table1.company_id = ? and table1.name in (?)", company_id, name)
    .group("...")
}

This allows you to put WHERE conditions on the nested query as well as the outer query

回复收藏 0 原文

~没有更多了~