Active Record-如何在第二张表上执行嵌套选择?

问题描述

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

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

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

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

我尝试过此操作，但它抱怨missing FROM-clause entry for table "customers":

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)

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

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

推荐答案

您尚未向我们提供有关这两个表之间关系的任何信息，因此我将假设Customer has_many Orders.

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

虽然ActiveRecord不支持您要执行的操作，但它是基于 Arel ，确实如此.

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

每个Rails模型都有一个名为arel_table的方法，该方法将返回其对应的Arel::Table.您可能希望帮助程序库使它更干净，因为默认方式有点麻烦.我将使用普通的Arel语法来最大化兼容性.

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可以理解Arel对象，并且可以接受其自身语法.

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')
])

哪个生产

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')
])

哪个给了我们

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

这篇关于Active Record-如何在第二张表上执行嵌套选择?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！