从Rails中的多个表中进行选择

Say I have Apples, Oranges and Melons, and each has a column "created_at". How would I construct an ActiveRecord / SQL query to find the 10 most recent Apples, Oranges and Melons? Is there an elegant way to do this?

假设我有苹果，橘子和甜瓜，每个都有一列“created_at”。我如何构建一个ActiveRecord / SQL查询来查找最近的10个苹果，橘子和甜瓜？有一种优雅的方式来做到这一点？

2 个解决方案

#1

If you have those types in separate tables, then I don't think you can do that in a single SQL query.

如果您在单独的表中有这些类型，那么我认为您不能在单个SQL查询中执行此操作。

You can find ids and types of the most recent records like this:

您可以找到最近的记录的ID和类型，如下所示：

SELECT * FROM
(SELECT 'Apple' AS class, id, created_at FROM apples LIMIT 10
UNION
SELECT 'Orange' AS class, id, created_at FROM oranges LIMIT 10
UNION
SELECT 'Melon' AS class, id, created_at FROM melons LIMIT 10) AS most_recent
ORDER BY created_at
LIMIT 10;

Then use these records to fetch particular objects by id.

然后使用这些记录通过id获取特定对象。

But if you can, try to store all these in a single table using Single Table Inheritance pattern, which is built-in into rails. This would work OK if the types shared a lot of fields. Then you could just use a single Fruit.find call with order and limit to fetch what you want.

但是，如果可以，请尝试使用单表继承模式将所有这些存储在单个表中，该模式内置于rails中。如果类型共享很多字段，这将工作正常。然后你可以使用一个具有order和limit的单个Fruit.find调用来获取你想要的东西。

In Rails 2:

在Rails 2中：

Fruit.find(:all, :order => "created_at", :limit => 10)

#2

Not tested but this should work

没有测试，但这应该工作

Apples.joins(:oranges).joins(:melons).order("apples.created_at,oranges.created_at,melons.created_at DESC").limit(10)

#1