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屋!