I''m trying to convert a non-sql application (4D or 4th Dimension) to
rails. Got all the CRUD stuff down, but I''m running into problem in
generating reports. 4D makes heavy use of Sets and Selections, which
are really just an array of record numbers that can be used to quickly
restore a query. Those arrays make aggregate reporting fairly easy. I
can get there using an sql query. I''m just looking for the best
approach using rails.
Lets say I have a simple has_many, belong_to relation:
Project:ARB (project fields :id, :name, "status, ...)
has_many :tasks
Task:ARB (task fields :id, :project_id, :name, :date_due, ...)
belong_to :project
I need to go both ways
give me a list of tasks where the project status is active, or:
select * from tasks where project_id in (select id from projects
where status = ''Active'')
give me a list of projects where the task date_due is < some_date
select * from projects where id in (select project_id from tasks
where date_due < ''2009-09-09''
How do I do that in an ActiveRecord find?
Steve
On Sep 10, 11:05 am, AppleII717 <sa...-ee4meeAH724@public.gmane.org> wrote:> I''m trying to convert a non-sql application (4D or 4th Dimension) to > rails. Got all the CRUD stuff down, but I''m running into problem in > generating reports. 4D makes heavy use of Sets and Selections, which > are really just an array of record numbers that can be used to quickly > restore a query. Those arrays make aggregate reporting fairly easy. I > can get there using an sql query. I''m just looking for the best > approach using rails. > > Lets say I have a simple has_many, belong_to relation: > > Project:ARB (project fields :id, :name, "status, ...) > has_many :tasks > Task:ARB (task fields :id, :project_id, :name, :date_due, ...) > belong_to :project > > I need to go both ways > give me a list of tasks where the project status is active, or: > > select * from tasks where project_id in (select id from projects > where status = ''Active'') > > give me a list of projects where the task date_due is < some_date > > select * from projects where id in (select project_id from tasks > where date_due < ''2009-09-09'' > > How do I do that in an ActiveRecord find? >At a very basic level, the conditions option is just a blob of sql - you can stick anything you want in there. I personally would do the above as Project.find :all, :joins => :task, :conditions => ["due_date < ?",...] which isn''t the same sql but has (I believe) the same result Fred> Steve
Thanks, you pointed my in the right direction. Not many examples using :joins. I ended up with Stage.find(:all, :joins => :job, :conditions => ["jobs.status = ? and stages.name = ?","Active","Application"]) Which is my real life models where Jobs have many Stages. Steve On Sep 10, 6:31 am, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On Sep 10, 11:05 am, AppleII717 <sa...-ee4meeAH724@public.gmane.org> wrote: > > > > > I''m trying to convert a non-sql application (4D or 4th Dimension) to > > rails. Got all the CRUD stuff down, but I''m running into problem in > > generating reports. 4D makes heavy use of Sets and Selections, which > > are really just an array of record numbers that can be used to quickly > > restore a query. Those arrays make aggregate reporting fairly easy. I > > can get there using an sql query. I''m just looking for the best > > approach using rails. > > > Lets say I have a simple has_many, belong_to relation: > > > Project:ARB (project fields :id, :name, "status, ...) > > has_many :tasks > > Task:ARB (task fields :id, :project_id, :name, :date_due, ...) > > belong_to :project > > > I need to go both ways > > give me a list of tasks where the project status is active, or: > > > select * from tasks where project_id in (select id from projects > > where status = ''Active'') > > > give me a list of projects where the task date_due is < some_date > > > select * from projects where id in (select project_id from tasks > > where date_due < ''2009-09-09'' > > > How do I do that in an ActiveRecord find? > > At a very basic level, the conditions option is just a blob of sql - > you can stick anything you want in there. > > I personally would do the above as Project.find :all, :joins > => :task, :conditions => ["due_date < ?",...] > > which isn''t the same sql but has (I believe) the same result > > Fred > > > > > Steve
AppleII717 wrote:> Thanks, you pointed my in the right direction. Not many examples > using :joins. I ended up with > > Stage.find(:all, :joins => :job, :conditions => ["jobs.status = ? and > stages.name = ?","Active","Application"])You may be able to rewrite this using the hash syntax for :conditions. You might also want to look at named_scope and possibly acts_as_state_machine.> > Which is my real life models where Jobs have many Stages. > > Steve > > On Sep 10, 6:31�am, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>Best, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/.