Selecting from across multiple tables in Rails

Question

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?

Answer 1

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

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

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.

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.

In Rails 2:

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