Forum: Ruby on Rails best way to combine results from two tables

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Larry W. (Guest)
on 2006-03-08 23:12
(Received via mailing list)
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?
Craig W. (Guest)
on 2006-03-08 23:18
(Received via mailing list)
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
Larry W. (Guest)
on 2006-03-08 23:21
(Received via mailing list)
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.
Craig W. (Guest)
on 2006-03-08 23:58
(Received via mailing list)
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
Larry W. (Guest)
on 2006-03-09 00:04
(Received via mailing list)
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.
Patrick R. (Guest)
on 2006-03-09 18:49
(Received via mailing list)
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

<tr>
    <td>Sheep</td><td>@animal.name</td><td>@animal.age</td>
</tr>

_cow_row.rhtml

<tr>
    <td>Cow</td><td>@animal.name</td><td>@animal.age</td>
</tr>

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
Larry W. (Guest)
on 2006-03-09 20:03
(Received via mailing list)
i like the idea of using  partials for rendering the rows. very nice.
This topic is locked and can not be replied to.