ActiveRecord的加入对汇总查询 [英] ActiveRecord joins on aggregate query

问题描述

我有一个单位模式，的has_many：transacions 。交易有一个键入列，用于说明什么样的FO交易是：销售，转让，预订，等

I have a Unit model that has_many :transacions. Transaction has a type column that indicates what kind fo transaction it is: sale, transfer, reservation, etc.

我想找到有一个给定的事务类型，因为其最新的事务中的所有单位。我无法弄清楚如何做到这一点的ActiveRecord的。

I would like to find all units that have a given transaction type as their latest transaction. I can't figure out how to do it in ActiveRecord.

在SQL，例如，如果我想找到的所有单位使用传送，因为我可以做他们的最新成交：

In SQL, if for example I wanted to find all units with "Transfer" as their latest transaction I could do:

SELECT u.*, tx.* FROM units u INNER JOIN transactions tx ON u.id=tx.unit_id
INNER JOIN
(
  SELECT tx.unit_id, max(tx.created_at) AS latest_tx_date
  FROM transactions tx
  GROUP BY tx.unit_id
  ORDER BY tx.unit_id
) max_dates
ON (tx.unit_id=max_dates.unit_id AND tx.created_at>=max_dates.latest_tx_date)
WHERE tx.type='Transfer';

这是加入与我有麻烦了内部查询。有没有可读的ActiveRecord的方式来做到这一点，或者我应该只使用SQL？

It's the joining with the inner query that I'm having trouble with. Is there a readable ActiveRecord way to do this or should I just use the SQL?

推荐答案

ActiveRecord的支持单表继承。没有必要写普通的SQL。

ActiveRecord supports single table inheritance. No need to write plain SQL.

这篇关于ActiveRecord的加入对汇总查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！