Best way to combine results from two tables

Rails
1

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?

2

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

3

That might have been best, but the animals are very different. lots of
cow
specific cols and sheep specific cols. :slight_smile:

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.

4

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

5

I’ve got the farmer table down :slight_smile:

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.

6

i like the idea of using partials for rendering the rows. very nice.

7

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