Active Record-如何在第二张表上执行嵌套选择?(Active Record - How to perform a nested select on a second table?)

17 IT屋

I need to list all customers along with their latest order date (plus pagination).

How can I write the following SQL query using Active Record?

select *,
  (
    select max(created_at)
    from orders
    where orders.customer_id = customers.id
  ) as latest_order_date
from customers
limit 25 offset 0

I tried this but it complains missing FROM-clause entry for table "customers":

Customer
  .select('*')
  .select(
    Order
      .where('customer_id = customers.id')
      .maximum(:created_at)
  ).page(params[:page])

# Generates this (clearly only the Order query):
SELECT MAX("orders"."created_at") 
FROM "orders" 
WHERE (customer_id = customers.id)

EDIT: it would be good to keep AR's parameterization and kaminari's pagination goodness.

解决方案

You haven't given us any information about the relationship between these two tables, so I will assume Customer has_many Orders.

While ActiveRecord doesn't support what you are trying to do, it is built on top of Arel, which does.

Every Rails model has a method named arel_table that will return its corresponding Arel::Table. You might want a helper library to make this cleaner because the default way is a little cumbersome. I will use the plain Arel syntax to maximize compatibility.

ActiveRecord understands Arel objects and can accept them alongside its own syntax.

orders = Order.arel_table
customers = Customer.arel_table

Customer.joins(:orders).group(:id).select([
  customers[Arel.star],
  orders[:created_at].maximum.as('latest_order_date')
])

Which produces

SELECT "customers".*, MAX("orders"."created_at") AS "latest_order_date"
FROM "customers"
INNER JOIN "orders" ON "orders"."customer_id" = "customers"."id"
GROUP BY "customers"."id"

This is the customary way of doing this, but if you still want to do it as a subquery, you can do this

Customer.select([
  customers[Arel.star],
  orders.project(orders[:created_at].maximum)
        .where(orders[:customer_id].eq(customers[:id]))
        .as('latest_order_date')
])

Which gives us

SELECT "customers".*, (
  SELECT MAX("orders"."created_at")
  FROM "orders"
  WHERE "orders"."customer_id" = "customers"."id" ) "latest_order_date"
FROM "customers"

我需要列出所有客户及其最新订单日期(加上分页)。



如何使用Active Record编写以下SQL查询?

>

  select *,

select max(created_at)
来自订单
其中订单.customer_id = customer.id
)作为客户的最新日期

的限制25偏移量0


我尝试过此操作,但是它抱怨缺少表" customers"的FROM子句条目:



 客户
.select('*')
.select(
订单
.where('customer_id = customer.id')
.maximum(:created_at)
).page(params [:page])

#生成此代码(显然仅是Order查询):
SELECT MAX(" orders"。" created_at")
来自" orders"
的地方(customer_id = customer.id)


编辑:最好保留AR的参数设置和 kaminari 的分页优势。


解决方案

您还没有提供有关这两个表之间关系的任何信息,所以我假设 Customer has_many Orders 。



虽然ActiveRecord不支持您要执行的操作,但它是在 Arel ,确实如此。



每个Rails模型都有一个方法名为 arel_table 的代码,它将返回其相应的 Arel :: Table 。您可能希望帮助程序库使此程序更清洁,因为默认方式有点麻烦。我将使用普通的Arel语法来最大限度地提高兼容性。



ActiveRecord可以理解Arel对象,并且可以接受其自身语法。



  orders = Order.arel_table 
客户= Customer.arel_table

Customer.joins(:orders).group(:id).select ([[
个客户[Arel.star],
个订单[:created_at] .maximum.as('latest_order_date')
])


哪个生产



 选择"客户"。*,MAX (" orders"。" created_at")AS" latest_order_date" 
来自" customers"
INNER JOIN" orders" ON" orders"。" customer_id" =" customers"。" id"
GROUP BY" customers"。" id"


这是通常的做法,但是如果您仍然希望将其作为子查询,您可以执行以下操作



  Customer.select([[
customers [Arel。星号],
个orders.project(orders [:created_at] .maximum)
.where(orders [:customer_id] .eq(customers [:id]))
.as('latest_order_date')
])


哪个给了我们



  SELECT" customers"。*,(
SELECT MAX(" orders"。" created_at")
FROM" orders" "
WHERE" orders"。" customer_id" =" customers"。" id")" latest_order_date"
FROM" customers"

本文地址:IT屋 » Active Record-如何在第二张表上执行嵌套选择?