CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
João Souza
João Souza

Reputation: 4208

Active Record - How to perform a nested select on a second table?

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.

Upvotes: 0

Views: 3598

Answers (3)

Adam Lassek
Adam Lassek

Reputation: 35505

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"

Upvotes: 2

Rob
Rob

Reputation: 4434

Here is the same answer @adam was giving, but not using AREL and just straight ActiveRecord. Not sure it's really much better than @João Marcelo Souza

Customer.select("customers.*, max(orders.created_at)").joins(:orders).group("customers.id").page(params[:page])

(The group by avoids list all the customers columns by using this feature of Postgres 9.1 and higher.)

The OP doesn't say, but the query doesn't handle the case where the customer has no orders. This version does that:

Customer.select("customers.*, coalesce(max(orders.created_at),0)").joins("left outer join orders on orders.customer_id=customers.id").group("customers.id").page(params[:page])

Upvotes: 0

João Souza
João Souza

Reputation: 4208

The most Active Record-ish way I've come up with so far is:

Customer
  .page(params[:page])
  .select('*')
  .select(<<-SQL.squish)
    (
      SELECT MAX(created_at) AS latest_order_date
      FROM orders
      WHERE orders.customer_id = customers.id
    )
  SQL

I still wish I could make the string part more Active Record-ish.

The <<-SQL is just heredoc.

Upvotes: 1

Related Questions