RE: best way to combine results from two tables


Let us know how it works!

From: removed_email_address@domain.invalid
[mailto:removed_email_address@domain.invalid] On Behalf Of Larry W.
Sent: Thursday, March 09, 2006 11:18 AM
To: removed_email_address@domain.invalid
Subject: Re: [Rails] best way to combine results from two tables

I did it with a union. now i’m going to try to build a model and see
how rails deals with it.

On 3/9/06, Lugovoi N. < removed_email_address@domain.invalid
mailto:removed_email_address@domain.invalid > wrote:

You probably want UNION:

create view farm_view as
select ‘cow’ as animal_type, name, age, farmer from cows
selec ‘sheep’ as animal_type, name, age, farmer from sheeps
selec ‘horse’ as animal_type, name, horse_age as age, farmer from
order by name, age, farmer desc ;

On 3/9/06, Larry W. removed_email_address@domain.invalid wrote:

I was wondering about that (view-table equivalence). Also not sure how
create a view that concatenates data from two tables (rather than
but I posted on the postgresql list for that one.

Since I’m starting to think that rolling my own would mean i can’t

I’m a long time DBA, and in my opinion, some things are just better
to the database as a view (as you stated), a stored procedure, or a
returning function. There are many many complex data operations that
occur much faster if run natively on the database. (Operations that
temp tables, and cubes, etc. come to mind)

At any rate, my guess is that rails would treat a view and a tables

From: removed_email_address@domain.invalid

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
Also going to try to create a pagination object manually on the merged
I’ll let you know how it turns out.

The only alternative i could think of is to merge all the data in a
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.

On 3/8/06, Craig W. < removed_email_address@domain.invalid> wrote:

OK - in that case, I am definitely interested if someone takes the

to explain because I am working on reports that need to iterate over

lot of items in a different manner but similarly enough that I might
learn something useful.


ps…I still would have a ‘farmers’ table

On Wed, 2006-03-08 at 16:19 -0500, Larry W. wrote:

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.

On 3/8/06, Craig W. removed_email_address@domain.invalid wrote:
On Wed, 2006-03-08 at 16:10 -0500, Larry W. wrote:
> I want to combine a selection of data from two tables

    > in the db and doing it there, as well as doing selects


    > tables and combining the results in a controller.  I

think I