CODEWITHSUNDEEP
sqlruby-on-rails
Newy
Newy

Reputation: 40107

Selecting from across multiple tables in 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?

Upvotes: 3

Views: 1103

Answers (2)

Haris Krajina
Haris Krajina

Reputation: 15286

Not tested but this should work

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

Upvotes: 0

Matt
Matt

Reputation: 5398

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)

Upvotes: 2

Related Questions