thr3ads.net - Rails - [Rails] best way to combine results from two tables [Mar 2006]

If this information is useful, please help other people find it:
Share via:

Larry White

2006-Mar-08 21:10 UTC

[Rails] best way to combine results from two tables

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?
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060308/e00f1af4/attachment-0001.html

Craig White

2006-Mar-08 21:16 UTC

head link

[Rails] best way to combine results from two tables

On Wed, 2006-03-08 at 16:10 -0500, Larry White wrote:> 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?----
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 White

2006-Mar-08 21:20 UTC

head link

[Rails] best way to combine results from two tables

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 White <craigwhite@azapple.com>
wrote:>
> On Wed, 2006-03-08 at 16:10 -0500, Larry White wrote:
> > 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?
> ----
> 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
>
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060308/4718bb49/attachment.html

Craig White

2006-Mar-08 21:56 UTC

head link

[Rails] best way to combine results from two tables

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 White 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 White <craigwhite@azapple.com> wrote:
>         On Wed, 2006-03-08 at 16:10 -0500, Larry White wrote:
>         > 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?
>         ----
>         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
>         
>         
>         _______________________________________________
>         Rails mailing list 
>         Rails@lists.rubyonrails.org
>         http://lists.rubyonrails.org/mailman/listinfo/rails
> 
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails

Larry White

2006-Mar-08 22:03 UTC

head link

[Rails] best way to combine results from two tables

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.

On 3/8/06, Craig White <craigwhite@azapple.com>
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 White 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 White <craigwhite@azapple.com> wrote:
> >         On Wed, 2006-03-08 at 16:10 -0500, Larry White wrote:
> >         > 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?
> >         ----
> >         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
> >
> >
> >         _______________________________________________
> >         Rails mailing list
> >         Rails@lists.rubyonrails.org
> >         http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> > _______________________________________________
> > Rails mailing list
> > Rails@lists.rubyonrails.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060308/2553d31f/attachment.html

Brian Corrigan

2006-Mar-09 16:03 UTC

head link

[Rails] best way to combine results from two tables

I''m going to throw my .02 in on this one.

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
same way..  

________________________________

From: rails-bounces@lists.rubyonrails.org
[mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of Larry White
Sent: Wednesday, March 08, 2006 5:04 PM
To: rails@lists.rubyonrails.org
Subject: Re: [Rails] best way to combine results from two tables

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. 

On 3/8/06, Craig White <craigwhite@azapple.com> 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 White 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 White <craigwhite@azapple.com> wrote:
>         On Wed, 2006-03-08 at 16:10 -0500, Larry White wrote:
>         > 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?
>         ----
>         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
>
>
>         _______________________________________________
>         Rails mailing list 
>         Rails@lists.rubyonrails.org
>         http://lists.rubyonrails.org/mailman/listinfo/rails 
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
_______________________________________________
Rails mailing list
Rails@lists.rubyonrails.org
http://lists.rubyonrails.org/mailman/listinfo/rails

-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060309/fc4445ed/attachment.html

Larry White

2006-Mar-09 16:09 UTC

head link

[Rails] best way to combine results from two tables

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
pagination to work, i will probably try the db way.

On 3/9/06, Brian Corrigan <brian.corrigan@mm-games.com>
wrote:>
>  I''m going to throw my .02 in on this one.
>
>
>
> 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
> same way..
>
>
>
>
>
>
>  ------------------------------
>
> *From:* rails-bounces@lists.rubyonrails.org [mailto:
> rails-bounces@lists.rubyonrails.org] *On Behalf Of *Larry White
> *Sent:* Wednesday, March 08, 2006 5:04 PM
> *To:* rails@lists.rubyonrails.org
> *Subject:* Re: [Rails] best way to combine results from two tables
>
>
>
> 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.
>
> On 3/8/06, *Craig White* <craigwhite@azapple.com> 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 White 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 White <craigwhite@azapple.com> wrote:
> >         On Wed, 2006-03-08 at 16:10 -0500, Larry White wrote:
> >         > 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?
> >         ----
> >         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
> >
> >
> >         _______________________________________________
> >         Rails mailing list
> >         Rails@lists.rubyonrails.org
> >         http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> > _______________________________________________
> > Rails mailing list
> > Rails@lists.rubyonrails.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060309/2226cbc1/attachment.html

Brian Corrigan

2006-Mar-09 16:14 UTC

head link

[Rails] best way to combine results from two tables

I think you can do this with a join....  Give me the code for both of
your tables..  Let me give it a shot :-)

-B

________________________________

From: rails-bounces@lists.rubyonrails.org
[mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of Larry White
Sent: Thursday, March 09, 2006 11:09 AM
To: rails@lists.rubyonrails.org
Subject: Re: [Rails] best way to combine results from two tables

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
pagination to work, i will probably try the db way.

On 3/9/06, Brian Corrigan < brian.corrigan@mm-games.com> wrote:

I''m going to throw my .02 in on this one.

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
same way..  

________________________________

From: rails-bounces@lists.rubyonrails.org
[mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of Larry White
Sent: Wednesday, March 08, 2006 5:04 PM
To: rails@lists.rubyonrails.org
Subject: Re: [Rails] best way to combine results from two tables

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. 

On 3/8/06, Craig White <craigwhite@azapple.com> 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 White 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 White <craigwhite@azapple.com> wrote:
>         On Wed, 2006-03-08 at 16:10 -0500, Larry White wrote:
>         > 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?
>         ----
>         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
>
>
>         _______________________________________________
>         Rails mailing list 
>         Rails@lists.rubyonrails.org
>         http://lists.rubyonrails.org/mailman/listinfo/rails 
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
_______________________________________________
Rails mailing list
Rails@lists.rubyonrails.org
http://lists.rubyonrails.org/mailman/listinfo/rails

_______________________________________________
Rails mailing list
Rails@lists.rubyonrails.org
http://lists.rubyonrails.org/mailman/listinfo/rails

-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060309/c460d767/attachment-0001.html

Lugovoi Nikolai

2006-Mar-09 16:15 UTC

head link

[Rails] best way to combine results from two tables

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 White <ljw1001@gmail.com> 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
> pagination to work, i will probably try the db way.
>
>
> On 3/9/06, Brian Corrigan < brian.corrigan@mm-games.com> wrote:
> >
> >
> >
> >
> > I''m going to throw my .02 in on this one.
> >
> >
> >
> >
> > 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
> same way..
> >
> >
> >
> >
> >
> >
> > ________________________________
>
> >
> > From: rails-bounces@lists.rubyonrails.org
> [mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of
> Larry White
> > Sent: Wednesday, March 08, 2006 5:04 PM
> > To: rails@lists.rubyonrails.org
> > Subject: Re: [Rails] best way to combine results from two tables
> >
> >
> >
> >
> > 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.
> >
> >
> > On 3/8/06, Craig White <craigwhite@azapple.com> 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 White 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 White <craigwhite@azapple.com> wrote:
> > >         On Wed, 2006-03-08 at 16:10 -0500, Larry White wrote:
> > >         > 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?
> > >         ----
> > >         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
> > >
> > >
> > >         _______________________________________________
> > >         Rails mailing list
> > >         Rails@lists.rubyonrails.org
> > >
> http://lists.rubyonrails.org/mailman/listinfo/rails
> > >
> > > _______________________________________________
> > > Rails mailing list
> > > Rails@lists.rubyonrails.org
> > > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> > _______________________________________________
> > Rails mailing list
> > Rails@lists.rubyonrails.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> >
> >
> > _______________________________________________
> > Rails mailing list
> > Rails@lists.rubyonrails.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> >
> >
>
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>

Larry White

2006-Mar-09 16:18 UTC

head link

[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 Nikolai <meadow.nnick@gmail.com>
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 White <ljw1001@gmail.com> 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
> > pagination to work, i will probably try the db way.
> >
> >
> > On 3/9/06, Brian Corrigan < brian.corrigan@mm-games.com> wrote:
> > >
> > >
> > >
> > >
> > > I''m going to throw my .02 in on this one.
> > >
> > >
> > >
> > >
> > > 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
> > same way..
> > >
> > >
> > >
> > >
> > >
> > >
> > > ________________________________
> >
> > >
> > > From: rails-bounces@lists.rubyonrails.org
> > [mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of
> > Larry White
> > > Sent: Wednesday, March 08, 2006 5:04 PM
> > > To: rails@lists.rubyonrails.org
> > > Subject: Re: [Rails] best way to combine results from two tables
> > >
> > >
> > >
> > >
> > > 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.
> > >
> > >
> > > On 3/8/06, Craig White <craigwhite@azapple.com> 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 White 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 White <craigwhite@azapple.com> wrote:
> > > >         On Wed, 2006-03-08 at 16:10 -0500, Larry White
wrote:
> > > >         > 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?
> > > >         ----
> > > >         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
> > > >
> > > >
> > > >         _______________________________________________
> > > >         Rails mailing list
> > > >         Rails@lists.rubyonrails.org
> > > >
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> > > >
> > > > _______________________________________________
> > > > Rails mailing list
> > > > Rails@lists.rubyonrails.org
> > > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > >
> > > _______________________________________________
> > > Rails mailing list
> > > Rails@lists.rubyonrails.org
> > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > >
> > >
> > >
> > > _______________________________________________
> > > Rails mailing list
> > > Rails@lists.rubyonrails.org
> > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > >
> > >
> > >
> >
> >
> > _______________________________________________
> > Rails mailing list
> > Rails@lists.rubyonrails.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> >
> >
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060309/14d560ca/attachment-0001.html

Brian Corrigan

2006-Mar-09 16:29 UTC

head link

[Rails] best way to combine results from two tables

Let us know how it works!

________________________________

From: rails-bounces@lists.rubyonrails.org
[mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of Larry White
Sent: Thursday, March 09, 2006 11:18 AM
To: rails@lists.rubyonrails.org
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 Nikolai < meadow.nnick@gmail.com
<mailto:meadow.nnick@gmail.com> > 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 White <ljw1001@gmail.com> 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 > pagination to work, i will probably try the db way.
>
>
> On 3/9/06, Brian Corrigan < brian.corrigan@mm-games.com> wrote:
> >
> > 
> >
> >
> > I''m going to throw my .02 in on this one.
> >
> >
> >
> >
> > 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> same way..
> >
> >
> >
> >
> >
> >
> > ________________________________
>
> >
> > From: rails-bounces@lists.rubyonrails.org 
> [mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of
> Larry White
> > Sent: Wednesday, March 08, 2006 5:04 PM
> > To: rails@lists.rubyonrails.org
> > Subject: Re: [Rails] best way to combine results from two tables
> >
> >
> >
> >
> > 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.
> >
> >
> > On 3/8/06, Craig White < craigwhite@azapple.com> 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 White 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 White <craigwhite@azapple.com> wrote:
> > >         On Wed, 2006-03-08 at 16:10 -0500, Larry White wrote: 
> > >         > 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?
> > >         ---- 
> > >         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
> > >
> > >
> > >         _______________________________________________
> > >         Rails mailing list
> > >         Rails@lists.rubyonrails.org
> > >
> http://lists.rubyonrails.org/mailman/listinfo/rails
> > >
> > > _______________________________________________
> > > Rails mailing list
> > > Rails@lists.rubyonrails.org
> > > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> > _______________________________________________ 
> > Rails mailing list
> > Rails@lists.rubyonrails.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails 
> >
> >
> >
> > _______________________________________________
> > Rails mailing list
> > Rails@lists.rubyonrails.org 
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> >
> >
>
>
> _______________________________________________ 
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails 
>
>
>
_______________________________________________
Rails mailing list
Rails@lists.rubyonrails.org
http://lists.rubyonrails.org/mailman/listinfo/rails



 

-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060309/d063c18f/attachment.html

Patrick Ritchie

2006-Mar-09 16:47 UTC

head link

[Rails] best way to combine results from two tables

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
> 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?
>
>
>------------------------------------------------------------------------
>
>_______________________________________________
>Rails mailing list
>Rails@lists.rubyonrails.org
>http://lists.rubyonrails.org/mailman/listinfo/rails
>  
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060309/692cbaea/attachment-0001.html

Larry White

2006-Mar-09 18:01 UTC

head link

[Rails] best way to combine results from two tables

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

On 3/9/06, Patrick Ritchie <pritchie@videotron.ca>
wrote:>
> 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
>
> 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?
>
>
> ------------------------------
>
> _______________________________________________ Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060309/0cd44e23/attachment.html

Maybe Matching Threads

Search for more seemingly similar threads

Rails - Mar 2006 - best way to combine results from two tables

[Rails] best way to combine results from two tables

[Rails] best way to combine results from two tables

[Rails] best way to combine results from two tables

[Rails] best way to combine results from two tables

[Rails] best way to combine results from two tables

[Rails] best way to combine results from two tables

[Rails] best way to combine results from two tables

[Rails] best way to combine results from two tables

[Rails] best way to combine results from two tables

[Rails] best way to combine results from two tables

[Rails] best way to combine results from two tables

[Rails] best way to combine results from two tables

[Rails] best way to combine results from two tables

Maybe Matching Threads