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
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
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
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
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
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
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
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
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 > > >
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
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 howto> create a view that concatenates data from two tables (rather thanjoining),> but I posted on the postgresql list for that one. > > Since I''m starting to think that rolling my own would mean i can''tget> 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 betterleft> to the database as a view (as you stated), a stored procedure, or atable> returning function. There are many many complex data operations thatwill> occur much faster if run natively on the database. (Operations thatrequire> temp tables, and cubes, etc. come to mind) > > > > > > > > At any rate, my guess is that rails would treat a view and a tablesthe> 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 Ican do> this safely because there''s a limited amount of current data (perfarmer).> Also going to try to create a pagination object manually on the mergeddata.> I''ll let you know how it turns out. > > > > The only alternative i could think of is to merge all the data in asingle> table as you suggested, possibly using updatable views to createmodels 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 thetime> > to explain because I am working on reports that need to iterate overa> > 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 tablesand> > > 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 selectsfrom> > > both > > > > tables and combining the results in a controller. Ithink 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
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
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