# Best way to combine results from two tables **URL:** \ **Category:** Rails\ **Created:** [March 8, 2006, 10:12pm UTC](https://www.ruby-forum.com/t/best-way-to-combine-results-from-two-tables/54575 "2006-03-08T22:12:24Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![larrywright](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/9fc348/32.png) [@larrywright](https://www.ruby-forum.com/u/larrywright)\ **Post date:** [March 8, 2006, 10:12pm UTC](https://www.ruby-forum.com/t/best-way-to-combine-results-from-two-tables/54575/1 "2006-03-08T22:12:24Z")
I want to combine a selection of data from two tables and display it as list. Note that this isn’t a join. i’m looking for something like this: Table 1: Sheep Name, Age, Farmer, etc. (sheep specific columns) Table 2: Cows: Name, Age, Farmer, etc. (cow specific columns) For my display I nneed something like this: Farmer Jone’s Livestock: TYPE NAME AGE Cow Bessie 2 Sheep Fred 6 What’s the best way to do this in rails? I considered creating a view in the db and doing it there, as well as doing selects from both tables and combining the results in a controller. I think I can just dump them all in one array since they answer the same messages. Then, just as I was about to implement, I thought “There must be a ‘rails way’ to do this.” Is there? ---
**Author:** ![larrywright](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/9fc348/32.png) [@larrywright](https://www.ruby-forum.com/u/larrywright)\ **Post date:** [March 8, 2006, 10:18pm UTC](https://www.ruby-forum.com/t/best-way-to-combine-results-from-two-tables/54575/2 "2006-03-08T22:18:35Z")
On Wed, 2006-03-08 at 16:10 -0500, Larry W. wrote: > tables and combining the results in a controller. I think I can just > dump them all in one array since they answer the same messages. > > Then, just as I was about to implement, I thought “There must be a > ‘rails way’ to do this.” Is there? * * * I would have done it differently from the start… table farmers id name has\_many :animals table animals id type name farmer\_id belongs\_to :farmers what do I know? Craig ---
**Author:** ![larrywright](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/9fc348/32.png) [@larrywright](https://www.ruby-forum.com/u/larrywright)\ **Post date:** [March 8, 2006, 10:21pm UTC](https://www.ruby-forum.com/t/best-way-to-combine-results-from-two-tables/54575/3 "2006-03-08T22:21:51Z")
That might have been best, but the animals are very different. lots of cow specific cols and sheep specific cols. 🙂 The real app is to provide a time ordered view from multiple timestamped tables. They don’t have much in common besides name, description and the timestamp. ---
**Author:** ![larrywright](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/9fc348/32.png) [@larrywright](https://www.ruby-forum.com/u/larrywright)\ **Post date:** [March 8, 2006, 10:58pm UTC](https://www.ruby-forum.com/t/best-way-to-combine-results-from-two-tables/54575/4 "2006-03-08T22:58:51Z")
OK - in that case, I am definitely interested if someone takes the time to explain because I am working on reports that need to iterate over a lot of items in a different manner but similarly enough that I might learn something useful. Craig ps…I still would have a ‘farmers’ table ---
**Author:** ![larrywright](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/9fc348/32.png) [@larrywright](https://www.ruby-forum.com/u/larrywright)\ **Post date:** [March 8, 2006, 11:04pm UTC](https://www.ruby-forum.com/t/best-way-to-combine-results-from-two-tables/54575/5 "2006-03-08T23:04:57Z")
I’ve got the farmer table down 🙂 I’m going to try an implementation that selects from both tables and merges the results in the controller. In my application, I think I can do this safely because there’s a limited amount of current data (per farmer). Also going to try to create a pagination object manually on the merged data. I’ll let you know how it turns out. The only alternative i could think of is to merge all the data in a single table as you suggested, possibly using updatable views to create models for each of the subtypes, but that seems to add a lot of overhead. ---
**Author:** ![larrywright](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/9fc348/32.png) [@larrywright](https://www.ruby-forum.com/u/larrywright)\ **Post date:** [March 9, 2006, 7:03pm UTC](https://www.ruby-forum.com/t/best-way-to-combine-results-from-two-tables/54575/6 "2006-03-09T19:03:11Z")
i like the idea of using partials for rendering the rows. very nice. ---
**Author:** ![larrywright](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/9fc348/32.png) [@larrywright](https://www.ruby-forum.com/u/larrywright)\ **Post date:** [March 9, 2006, 5:49pm UTC](https://www.ruby-forum.com/t/best-way-to-combine-results-from-two-tables/54575/7 "2006-03-09T17:49:29Z")
How about something like this: @animals = Sheep.find(:all) \<\< Cow.find(:all) @animals.sort! {|a,b| a.name \<=\> b.name} This results in an array of farm animals sorted by name. View code: …table header… \<% for @animal in @animals %\> \<%= render :partial =\> “#{animal.class.to\_s.downcase}\_row” %\> \<% end %\> …table footer… \_sheep\_row.rhtml Sheep @animal.name @animal.age \_cow\_row.rhtml Cow @animal.name @animal.age This example obviously has a lot of shared code between the partials, but it has the advantage that you can get really fancy in the composition of each type of row. You could for example: - Add picture of the corresponding animal. - Add a different css class id to style it a different color. - List the age of the Cows in years and the sheep in months. - Use your imagination… You can also easily work around any oddities in each model. eg: perhaps you want to use the ‘display\_name’ method for cows, but not for sheep. Cheers! Patrick