Hi there. I have two models (and two tables, of course), let's call them Room and Reserved, with similar fields but with the last one with some extra info, and need to get data from both using a "union all". Let's think on a hotel where I need to "glue" data to take a snapshot from the occupation like: Room (number,date,ocupied_by) 1,'2006/06/01',John 2,'2006/06/01',Paul Reserved (number,reserved_on,reserved_by,expire_on) 3,'2006/06/01',George,'2006/06/02 14:00' 4,'2006/06/01',Ringo ,'2006/06/02 14:00' select number, date, ocupied_by from room where date='2006/06/01' union all select number, reserved_on as date, reserved_by as ocupied_by from reserved where reserved_on='2006/06/01' order by 1 How can I do that with ActiveRecord/Rails? Ok, I'm not so sure it's a good example/tables to illustrate the question, but the idea is using an "union all" on the data from two tables as on regular SQL queries. Thanks!
on 2006-06-02 00:09
on 2006-06-02 03:24
On Thu, Jun 01, 2006 at 10:09:37PM +0200, Eustáquio Rangel wrote: > I have two models (and two tables, of course), let's call them Room and > Reserved, with similar fields but with the last one with some extra > info, and need to get data from both using a "union all". Let's think on This looks like a job for... <bom BOM!> Single Table Inheritance. CREATE TABLE rooms ( id INTEGER PRIMARY KEY, type VARCHAR(255) NOT NULL, number INTEGER NOT NULL, date DATE NOT NULL, occupant VARCHAR(255) NOT NULL, expiry DATE ); class RoomOrReservation < ActiveRecord::Base end class Room < RoomOrReservation class Reservation < RoomOrReservation end # Find just the current room allocations rooms = Room.find :all # Find just the reservations reservations = Reservation.find :all # Get *everything* all_of_it = RoomOrReservation.find :all This way, you only need one table, and AR takes care of all of the messiness of the whole thing. Now, if what you need to do is *really* union two tables (eg legacy database, or STI just doesn't do it for you), then you can either just do two separate queries and add the two resultsets together: all_of_it = Room.find(:all) + Reservation.find(:all) or, if you have a deep and abiding need to effectively "cast" all of the objects to one class, you can just use find_by_sql to do it: all_of_it = Room.find_by_sql ["SELECT a, b FROM table1 where x=? UNION ALL blah blah blah", x] But if you've got the option of using STI, I would definitely recommend it -- it's just a huge chunk of hotness. - Matt
on 2006-06-02 17:25
Matthew P. wrote: Hey Matt! > Now, if what you need to do is *really* union two tables (eg legacy > database, or STI just doesn't do it for you), then you can either just > do > two separate queries and add the two resultsets together: Yes, that's my case. > all_of_it = Room.find(:all) + Reservation.find(:all) I was thinking doing that way but really needed to be sure that there wasn't a better way, thanks for your answer! I'll make this way, so I can get all the data from both. The reservation table really have a lot of other fields that the room table don't need, so there will be a lot of empty null fields there. I will keep it separated from each other. Thanks again! Best regards,