# How complex a query can ActiveRecord handle before it's better using Raw SQL or a View? **URL:** \ **Category:** Rails\ **Created:** [December 19, 2008, 2:26am UTC](https://www.ruby-forum.com/t/how-complex-a-query-can-activerecord-handle-before-its-better-using-raw-sql-or-a-view/155992 "2008-12-19T02:26:12Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![greg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/71c47a/32.png) [@greg](https://www.ruby-forum.com/u/greg)\ **Post date:** [December 19, 2008, 2:26am UTC](https://www.ruby-forum.com/t/how-complex-a-query-can-activerecord-handle-before-its-better-using-raw-sql-or-a-view/155992/1 "2008-12-19T02:26:12Z")
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 ---
**Author:** ![greg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/71c47a/32.png) [@greg](https://www.ruby-forum.com/u/greg)\ **Post date:** [December 19, 2008, 2:49am UTC](https://www.ruby-forum.com/t/how-complex-a-query-can-activerecord-handle-before-its-better-using-raw-sql-or-a-view/155992/2 "2008-12-19T02:49:36Z")
On 19 Dec 2008, at 01:25, Greg H. 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 ---
**Author:** ![greg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/71c47a/32.png) [@greg](https://www.ruby-forum.com/u/greg)\ **Post date:** [December 19, 2008, 3:02am UTC](https://www.ruby-forum.com/t/how-complex-a-query-can-activerecord-handle-before-its-better-using-raw-sql-or-a-view/155992/3 "2008-12-19T03:02:28Z")
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 C. \< ---
**Author:** ![greg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/71c47a/32.png) [@greg](https://www.ruby-forum.com/u/greg)\ **Post date:** [December 19, 2008, 7:21am UTC](https://www.ruby-forum.com/t/how-complex-a-query-can-activerecord-handle-before-its-better-using-raw-sql-or-a-view/155992/4 "2008-12-19T07:21:03Z")
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 C. \< ---
**Author:** ![greg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/71c47a/32.png) [@greg](https://www.ruby-forum.com/u/greg)\ **Post date:** [December 19, 2008, 3:14am UTC](https://www.ruby-forum.com/t/how-complex-a-query-can-activerecord-handle-before-its-better-using-raw-sql-or-a-view/155992/5 "2008-12-19T03:14:11Z")
On 19 Dec 2008, at 02:01, Greg H. 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