Rails - Dec 2008 - How complex a query can ActiveRecord handle before it's better using Raw SQL or a View???

Hi,

How complex a query can ActiveRecord handle before it''s better using
Raw SQL
or a View???

I need to calculate for example taxable income via a query going across 5
tables.  Some like this: Find all transaction records (transactions table),
for which they are allocated (via allocation table) to person X (person
table), for transaction dates within date1 & date2, for which the
category (categories table) associated with transaction, has a tax_id (from
tax_codes table) of XYZ.  I will need to reuse this type of query as I build
a report of various tax categories.

Question - What would Rails best practice way for the above?

[I''m thinking best to create a flat view of this and use this to then
query
against]

Thanks

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 19 Dec 2008, at 01:25, Greg Hauptmann wrote:
> Hi,
>
> How complex a query can ActiveRecord handle before it''s better
using
> Raw SQL or a View???
>
Depends what you mean by handle. If you mean "will ruby run out of  
steam" then that''s purely a function of the result set,
instantiating
1 million ActiveRecord objects is probably not a good idea.
If you mean handle as in get ActiveRecord to generate it without  
writing any sql yourself then I don''t believe there''s a hard
and fast
rule or anything like that.
You might be able to get away with

Transaction.find :all, :joins => [:category, {:allocation => :person}],
		       :conditions => {''people.id'' => bob,
''categories.tax_id'' =>
1234, ''transactions.created_at'' => (1.week.ago ..
Time.now)}

or maybe the relations you need to express don''t quite map to those  
things activerecord makes easy. It''s a case by case thing, much as
I''d
far write the above than write the equivalent sql, I wouldn''t waste  
time trying to force activerecord through hoops when it was clear that  
it wasn''t time well spent.

Fred
> I need to calculate for example taxable income via a query going  
> across 5 tables.  Some like this: Find all transaction records  
> (transactions table), for which they are allocated (via allocation  
> table) to person X (person table), for transaction dates within  
> date1 & date2, for which the category (categories table) associated  
> with transaction, has a tax_id (from tax_codes table) of XYZ.  I  
> will need to reuse this type of query as I build a report of various  
> tax categories.
>
> Question - What would Rails best practice way for the above?
>
> [I''m thinking best to create a flat view of this and use this to  
> then query against]
>
> Thanks
>
> >

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

I was asking I guess from the point of view of:
(a) maintainability/readability of the code (i.e. keeping things simple) and
(b) to a lesser extent performance (i.e. if ActiveRecord wasn''t going
to be
good at handling a complex query well from a performance point of view)

On Fri, Dec 19, 2008 at 11:48 AM, Frederick Cheung <
frederick.cheung-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
>
> On 19 Dec 2008, at 01:25, Greg Hauptmann wrote:
>
> > Hi,
> >
> > How complex a query can ActiveRecord handle before it''s
better using
> > Raw SQL or a View???
> >
> Depends what you mean by handle. If you mean "will ruby run out of
> steam" then that''s purely a function of the result set,
instantiating
> 1 million ActiveRecord objects is probably not a good idea.
> If you mean handle as in get ActiveRecord to generate it without
> writing any sql yourself then I don''t believe there''s a
hard and fast
> rule or anything like that.
> You might be able to get away with
>
> Transaction.find :all, :joins => [:category, {:allocation =>
:person}],
>                       :conditions => {''people.id''
=> bob,
> ''categories.tax_id'' =>
> 1234, ''transactions.created_at'' => (1.week.ago ..
Time.now)}
>
> or maybe the relations you need to express don''t quite map to
those
> things activerecord makes easy. It''s a case by case thing, much as
I''d
> far write the above than write the equivalent sql, I wouldn''t
waste
> time trying to force activerecord through hoops when it was clear that
> it wasn''t time well spent.
>
> Fred
> > I need to calculate for example taxable income via a query going
> > across 5 tables.  Some like this: Find all transaction records
> > (transactions table), for which they are allocated (via allocation
> > table) to person X (person table), for transaction dates within
> > date1 & date2, for which the category (categories table)
associated
> > with transaction, has a tax_id (from tax_codes table) of XYZ.  I
> > will need to reuse this type of query as I build a report of various
> > tax categories.
> >
> > Question - What would Rails best practice way for the above?
> >
> > [I''m thinking best to create a flat view of this and use this
to
> > then query against]
> >
> > Thanks
> >
> > >
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 19 Dec 2008, at 02:01, Greg Hauptmann wrote:
> I was asking I guess from the point of view of:
> (a) maintainability/readability of the code (i.e. keeping things  
> simple) and
I don''t think how complex is the right question. There are some very  
complex things you can generate very easily, as well as easier things  
where you might have to fight activerecord.
>
> (b) to a lesser extent performance (i.e. if ActiveRecord wasn''t  
> going to be good at handling a complex query well from a performance  
> point of view)
That''s sort of what I started with. Performance wise, Active Record is
only going to care about the size of the result set really. After all  
it''s the database, not active record that has to do the 27 joins or  
whatever it is.

Fred


> On Fri, Dec 19, 2008 at 11:48 AM, Frederick Cheung
<frederick.cheung-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
> > wrote:
>
>
> On 19 Dec 2008, at 01:25, Greg Hauptmann wrote:
>
> > Hi,
> >
> > How complex a query can ActiveRecord handle before it''s
better using
> > Raw SQL or a View???
> >
> Depends what you mean by handle. If you mean "will ruby run out of
> steam" then that''s purely a function of the result set,
instantiating
> 1 million ActiveRecord objects is probably not a good idea.
> If you mean handle as in get ActiveRecord to generate it without
> writing any sql yourself then I don''t believe there''s a
hard and fast
> rule or anything like that.
> You might be able to get away with
>
> Transaction.find :all, :joins => [:category, {:allocation  
> => :person}],
>                       :conditions => {''people.id''
=> bob,
> ''categories.tax_id'' =>
> 1234, ''transactions.created_at'' => (1.week.ago ..
Time.now)}
>
> or maybe the relations you need to express don''t quite map to
those
> things activerecord makes easy. It''s a case by case thing, much as
I''d
> far write the above than write the equivalent sql, I wouldn''t
waste
> time trying to force activerecord through hoops when it was clear that
> it wasn''t time well spent.
>
> Fred
> > I need to calculate for example taxable income via a query going
> > across 5 tables.  Some like this: Find all transaction records
> > (transactions table), for which they are allocated (via allocation
> > table) to person X (person table), for transaction dates within
> > date1 & date2, for which the category (categories table)
associated
> > with transaction, has a tax_id (from tax_codes table) of XYZ.  I
> > will need to reuse this type of query as I build a report of various
> > tax categories.
> >
> > Question - What would Rails best practice way for the above?
> >
> > [I''m thinking best to create a flat view of this and use this
to
> > then query against]
> >
> > Thanks
> >
> > >
>
>
>
>
>
> >

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

thanks Fred - you''ve help me realise that the ":joins" option
can be used in
a Rails like sense (i.e. as well as with SQL) so I''ll try this way
first

On Fri, Dec 19, 2008 at 12:13 PM, Frederick Cheung <
frederick.cheung-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
>
> On 19 Dec 2008, at 02:01, Greg Hauptmann wrote:
>
> > I was asking I guess from the point of view of:
> > (a) maintainability/readability of the code (i.e. keeping things
> > simple) and
>
> I don''t think how complex is the right question. There are some
very
> complex things you can generate very easily, as well as easier things
> where you might have to fight activerecord.
> >
> > (b) to a lesser extent performance (i.e. if ActiveRecord
wasn''t
> > going to be good at handling a complex query well from a performance
> > point of view)
> That''s sort of what I started with. Performance wise, Active
Record is
> only going to care about the size of the result set really. After all
> it''s the database, not active record that has to do the 27 joins
or
> whatever it is.
>
> Fred
>
>
>
> > On Fri, Dec 19, 2008 at 11:48 AM, Frederick Cheung <
> frederick.cheung-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
> > > wrote:
> >
> >
> > On 19 Dec 2008, at 01:25, Greg Hauptmann wrote:
> >
> > > Hi,
> > >
> > > How complex a query can ActiveRecord handle before it''s
better using
> > > Raw SQL or a View???
> > >
> > Depends what you mean by handle. If you mean "will ruby run out
of
> > steam" then that''s purely a function of the result set,
instantiating
> > 1 million ActiveRecord objects is probably not a good idea.
> > If you mean handle as in get ActiveRecord to generate it without
> > writing any sql yourself then I don''t believe
there''s a hard and fast
> > rule or anything like that.
> > You might be able to get away with
> >
> > Transaction.find :all, :joins => [:category, {:allocation
> > => :person}],
> >                       :conditions => {''people.id''
=> bob,
> > ''categories.tax_id'' =>
> > 1234, ''transactions.created_at'' => (1.week.ago ..
Time.now)}
> >
> > or maybe the relations you need to express don''t quite map to
those
> > things activerecord makes easy. It''s a case by case thing,
much as I''d
> > far write the above than write the equivalent sql, I wouldn''t
waste
> > time trying to force activerecord through hoops when it was clear that
> > it wasn''t time well spent.
> >
> > Fred
> > > I need to calculate for example taxable income via a query going
> > > across 5 tables.  Some like this: Find all transaction records
> > > (transactions table), for which they are allocated (via
allocation
> > > table) to person X (person table), for transaction dates within
> > > date1 & date2, for which the category (categories table)
associated
> > > with transaction, has a tax_id (from tax_codes table) of XYZ.  I
> > > will need to reuse this type of query as I build a report of
various
> > > tax categories.
> > >
> > > Question - What would Rails best practice way for the above?
> > >
> > > [I''m thinking best to create a flat view of this and use
this to
> > > then query against]
> > >
> > > Thanks
> > >
> > > >
> >
> >
> >
> >
> >
> > >
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---