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: email@example.com [mailto:firstname.lastname@example.org] On Behalf Of Larry White Sent: Wednesday, March 08, 2006 5:04 PM To: email@example.com 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 <firstname.lastname@example.org> 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
on 2006-03-09 17:06
on 2006-03-09 17:09
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 2006-03-09 17:18
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 2006-03-09 17:18
I did it with a union. now i'm going to try to build a model and see how rails deals with it.