Forum: Ruby on Rails RE: 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.
Brian C. (Guest)
on 2006-03-09 18:31
(Received via mailing list)
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
union
selec 'sheep' as animal_type,  name, age, farmer from sheeps
union
selec 'horse' as animal_type,  name, horse_age as age, farmer from
horses
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
to
> create a view that concatenates data from two tables (rather than
joining),
> 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
get
> >
> >
> >
> > I'm a long time DBA, and in my opinion, some things are just better
left
> to the database as a view (as you stated), a stored procedure, or a
table
> returning function.  There are many many complex data operations that
will
> occur much faster if run natively on the database.  (Operations that
require
> temp tables, and cubes, etc. come to mind)
> >
> >
> >
> > At any rate, my guess is that rails would treat a view and a tables
the
> > 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
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.
> >
> >
> > On 3/8/06, Craig W. < removed_email_address@domain.invalid> wrote:
> >
> > 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
> >
> > 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. :-)
> > >
> > > 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
and
> > >         >
> > >         > 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
This topic is locked and can not be replied to.