CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
user1139144
user1139144

Reputation:

Rails - Get latest entries from multiple tables

I am using RoR for app development and want to implement the following:

For the app's RSS feed I need to pull out 10 latest entries from the database. The part I am not able to figure out is how to get last 10 entries across multiple tables.

Pulling last n records from a single database table is easy but how to pull last n records across multiple database tables.

Any suggestions?

Upvotes: 0

Views: 1053

Answers (2)

Edgars Jekabsons
Edgars Jekabsons

Reputation: 2853

There are none built-in methods in ActiveRecord for this, doing this in SQL very simple You just do UNION.

SELECT * FROM (
  SELECT table1.*, 'table_1_type' AS table_type FROM table1 
  UNION ALL SELECT table2.*, 'table_2_type' AS table_type FROM table2 
  UNION SELECT table3.*, 'table_3_type' AS table_type FROM table3 
) AS my_union ORDER created_at DESC LIMIT 10;

This query will return 10 rows, each row will have tables from each of those. I suggest You to create plain Ruby class that executes this raw query and rebuilds the objects. Or even better just fetch the values necessary for You and represent them using plain Ruby classes (not using You models).

You can access postgres connection through any AR class, like this MyModelClass.connection Example of using raw connection:

conn = MyModelClass.connection
res  = conn.exec('select tablename, tableowner from pg_tables')

res.each do |row|
  row.each do |column|
   puts column
  end
end

In same fashion You can execute Your query and populate Ruby objects.

UPD

There is also a third option You can use. Create a new model RssItem and back it with SQL view instead of table. The approach is explained in this post:

http://hashrocket.com/blog/posts/sql-views-and-activerecord

Upvotes: 3

Mike S
Mike S

Reputation: 11409

Is there a particular reason you want to combine queries for multiple tables into one sql query? I don't think its possible to do a union query.

You can try something like this, its not exactly what you want though:

@all_items = [@record_videos.all, @musics.all, @new_topics.all].flatten.sort_by{ |thing|   thing.created_at}

taken from: rails 3 merge multiple queries from different tables

If it is important to you then the true way to accomplish this in Rails is to use STI (single table inheritance). But that would require restructuring your DB and models.

Upvotes: -1

Related Questions