Fetch data from multiple tables and sort all by their time

Question

Hello, I'm creating a page where I want to make a history page. So I was wondering if there is any way to fetch all rows from multiple tables and then sort by their time? Every table has a field called "created_at".

So is there any way to fetch from all tables and sort without having Rails sorting them form me?

Answer 1

Use Order By in your SQL. So your query might look like this

Select * from table order by created_at

Answer 2

Depending on whether these databases are all on the same machine or not:

On same machine: Use OrderBy and UNION statements in your sql to return your result set

On different machines: You'll want to test this for performance, but you could use Linked Servers and UNION, ORDER BY. Alternatively, you could have ruby get the results from each db, and then combine them and sort

EDIT: From your last comment about different tables and not DB's; use something like this:

SELECT Created FROM table1
UNION
SELECT Created FROM table2
ORDER BY created

Answer 3

You would need to perform the sql like:

Select * from table order by created_at incr

: Store this into an array. Do this for each of the data sources, and then perform a merge sort on all the arrays in Ruby. Of course this will work well for small data sets, but once you get a data set that is large (ie: greater than will fit into memory) then you will have to use a different collect/merge algorithm.

So I guess the answer is that you do need to perform some sort of Ruby, unless you resort to the Union method described in another answer.

Answer 4

You may get a better answer, but I would presume you would need to

1. Create a History table with a Created date column, an autogenerated Id column, and any other contents you would like to expose [eg Name, Description]
2. Modify all tables that generate a "history" item to consume this new table via Foreign Key relationship on History.Id

"Mashing up" tables [ie merging different result sets into a single result set] is a very difficult problem, but you would effectively be doing the above anyway - just in the application layer, so why not do it correctly and more efficiently in the data layer.

Hope this helps :)