# When is it okay to resort to find\_by\_sql?

**URL:** <https://www.ruby-forum.com/t/when-is-it-okay-to-resort-to-find-by-sql/194680>\
**Category:** Rails\
**Created:** [September 9, 2010, 1:02am UTC](https://www.ruby-forum.com/t/when-is-it-okay-to-resort-to-find-by-sql/194680 "2010-09-09T01:02:33Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [September 9, 2010, 1:02am UTC](https://www.ruby-forum.com/t/when-is-it-okay-to-resort-to-find-by-sql/194680/1 "2010-09-09T01:02:33Z")

</div>

When is it appropriate to resort to find\_by\_sql?

I’m all for portable, easily maintained code. I’m a HUGE fan the new  
ActiveRecord::Relation model in Rails 3.0 I keenly feel the peril of  
dropping into find\_by\_sql().

Question 1: is there’s a snowball’s chance in Hades for the following  
code to be written WITHOUT resorting find\_by\_sql()?

inner\_query =\<\<EOQ  
SELECT DATE(st.datetime) AS start\_time,  
DATE(et.datetime - INTERVAL 1 DAY) AS end\_time,  
DATEDIFF(et.datetime, st.datetime) AS day\_count,  
sbs.quantity AS quantity,  
sbs.cost AS cost  
FROM service\_bills as sbs  
INNER JOIN time\_dimensions AS st ON st.id = sbs.start\_time\_id  
INNER JOIN time\_dimensions AS et ON et.id = sbs.end\_time\_id  
WHERE sbs.metered\_service\_id IN (#{metered\_service\_ids})  
EOQ

I’m sure it’s possible using a ServiceBill.find() construct where each  
clause is essentially a quoted string from the above, but by that point,  
I’d be hard pressed to say that is clearer or more maintainable.

Question 2: Nested queries. In my system, inner\_query is, in fact, an  
inner query that appears within this little monster:

outer\_query =\<\<EOQ  
SELECT st.id AS start\_time\_id,  
et.id AS end\_time\_id,  
DATE(st.datetime) AS time,  
SUM(bills.quantity/bills.day\_count) AS daily\_quantity,  
SUM(bills.cost/bills.day\_count) AS daily\_cost  
FROM time\_dimensions AS st  
INNER JOIN time\_dimensions AS et ON DATE(st.datetime + INTERVAL 1 DAY)  
= DATE(et.datetime)  
INNER JOIN (#{inner\_query}) AS bills  
WHERE st.datetime BETWEEN bills.start\_time AND bills.end\_time  
AND st.is\_midnight = 1  
GROUP BY st.id  
EOQ

Even if both of these queries could be cast into a find() construct, I  
don’t see how to create the equivalent of nested queries: it seems like  
I’d have to “pop back up” to the Rails level, which is problematic for  
really large datasets.

So: If ActiveRecord::Relation.find() really cannot handle queries of  
this complexity, I’m okay with that. But if it can, I’d welcome  
pointers or documentation on how to code them.

TIA.

- ff

---

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [September 9, 2010, 6:42am UTC](https://www.ruby-forum.com/t/when-is-it-okay-to-resort-to-find-by-sql/194680/2 "2010-09-09T06:42:28Z")

</div>

Fearless F. wrote:

> When is it appropriate to resort to find\_by\_sql?

When you need something that can’t be done any other way.

> I’m all for portable, easily maintained code. I’m a HUGE fan the new  
> ActiveRecord::Relation model in Rails 3.0 I keenly feel the peril of  
> dropping into find\_by\_sql().
> 
> Question 1: is there’s a snowball’s chance in Hades for the following  
> code to be written WITHOUT resorting find\_by\_sql()?
> 
> inner\_query =\<\<EOQ  
> SELECT DATE(st.datetime) AS start\_time,  
> DATE(et.datetime - INTERVAL 1 DAY) AS end\_time,  
> DATEDIFF(et.datetime, st.datetime) AS day\_count,  
> sbs.quantity AS quantity,  
> sbs.cost AS cost  
> FROM service\_bills as sbs  
> INNER JOIN time\_dimensions AS st ON st.id = sbs.start\_time\_id  
> INNER JOIN time\_dimensions AS et ON et.id = sbs.end\_time\_id  
> WHERE sbs.metered\_service\_id IN (#{metered\_service\_ids})  
> EOQ
> 
> I’m sure it’s possible using a ServiceBill.find() construct where each  
> clause is essentially a quoted string from the above, but by that point,  
> I’d be hard pressed to say that is clearer or more maintainable.

If it is possible to use find instead of find\_by\_sql, then always do so.  
If nothing else, ActiveRecord may be able to reason better about the  
query string.

> Question 2: Nested queries. In my system, inner\_query is, in fact, an  
> inner query that appears within this little monster:
> 
> outer\_query =\<\<EOQ  
> SELECT st.id AS start\_time\_id,  
> et.id AS end\_time\_id,  
> DATE(st.datetime) AS time,  
> SUM(bills.quantity/bills.day\_count) AS daily\_quantity,  
> SUM(bills.cost/bills.day\_count) AS daily\_cost  
> FROM time\_dimensions AS st  
> INNER JOIN time\_dimensions AS et ON DATE(st.datetime + INTERVAL 1 DAY)  
> = DATE(et.datetime)  
> INNER JOIN (#{inner\_query}) AS bills  
> WHERE st.datetime BETWEEN bills.start\_time AND bills.end\_time  
> AND st.is\_midnight = 1  
> GROUP BY st.id  
> EOQ
> 
> Even if both of these queries could be cast into a find() construct, I  
> don’t see how to create the equivalent of nested queries: it seems like  
> I’d have to “pop back up” to the Rails level, which is problematic for  
> really large datasets.

There are relatively few cases (not none, but few) where a subquery  
should not be replaced by a join. Are you sure you need that subquery?

> So: If ActiveRecord::Relation.find() really cannot handle queries of  
> this complexity, I’m okay with that. But if it can, I’d welcome  
> pointers or documentation on how to code them.

Can’t help there; haven’t started playing with Rails 3 yet.

> TIA.
> 
> - ff

## Best,

Marnen Laibow-Koser  
[http://www.marnen.org](http://www.marnen.org)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [September 9, 2010, 7:22am UTC](https://www.ruby-forum.com/t/when-is-it-okay-to-resort-to-find-by-sql/194680/3 "2010-09-09T07:22:16Z")

</div>

Marnen Laibow-Koser wrote (among other things…)

> > Question 2: Nested queries. In my system, inner\_query is, in fact, an  
> > inner query that appears within this little monster:  
> > …  
> > There are relatively few cases (not none, but few) where a subquery  
> > should not be replaced by a join. Are you sure you need that subquery?

Well, then, Manen, help me understand if I need that subquery.

The inner\_query construct fetches “monthly” utility bills. (I say  
“monthly” in quotes because the start and end dates don’t generally fall  
on month boundaries.) The outer\_query construct “explodes” the monthly  
bills into daily bills, dividing by the number of days spanned by the  
bill, and summing multiple bills that fall on the same day (since they  
can overlap). I’ve thought about this for a while, but don’t see how to  
do both of those functions via a join.

I’ll sleep on it, but if you have any insights, let me know. Thanks.

- ff

---

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [September 9, 2010, 7:23am UTC](https://www.ruby-forum.com/t/when-is-it-okay-to-resort-to-find-by-sql/194680/4 "2010-09-09T07:23:51Z")

</div>

Fearless F. wrote:

> Well, then, Manen, help me understand if I need that subquery.

Fiddlesticks. Marnen, pardon my typo (again) on the spelling of your  
name.

- Frealess Foole

---

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [September 9, 2010, 7:56am UTC](https://www.ruby-forum.com/t/when-is-it-okay-to-resort-to-find-by-sql/194680/5 "2010-09-09T07:56:42Z")

</div>

Fearless F. wrote:

> Marnen Laibow-Koser wrote (among other things…)
> 
> > > Question 2: Nested queries. In my system, inner\_query is, in fact, an  
> > > inner query that appears within this little monster:  
> > > …  
> > > There are relatively few cases (not none, but few) where a subquery  
> > > should not be replaced by a join. Are you sure you need that subquery?
> 
> Well, then, Manen, help me understand if I need that subquery.
> 
> The inner\_query construct fetches “monthly” utility bills. (I say  
> “monthly” in quotes because the start and end dates don’t generally fall  
> on month boundaries.) The outer\_query construct “explodes” the monthly  
> bills into daily bills, dividing by the number of days spanned by the  
> bill, and summing multiple bills that fall on the same day (since they  
> can overlap). I’ve thought about this for a while, but don’t see how to  
> do both of those functions via a join.

I’ll have to think about it some more. However, a quick look at your  
two queries suggest that you may be trying to do too much in the  
database and too little in the application.

> I’ll sleep on it, but if you have any insights, let me know. Thanks.

I’ll have to spend some more time on it.

> - ff

## Best,

Marnen Laibow-Koser  
[http://www.marnen.org](http://www.marnen.org)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [September 11, 2010, 3:22am UTC](https://www.ruby-forum.com/t/when-is-it-okay-to-resort-to-find-by-sql/194680/6 "2010-09-11T03:22:41Z")

</div>

Marnen Laibow-Koser wrote:

> > > There are relatively few cases (not none, but few) where a subquery  
> > > should not be replaced by a join. Are you sure you need that subquery?

Marnen: I owe your a debt of gratitude. Your note spurred me to  
_really_ grok SQL joins. After thirty six hours of study and  
anti-social behavior, I’ve got it. I think I’ll write a blog post  
called “The Zen of SQL JOIN: a gentle guide for procedural programmers”.  
(Teaser: Use JOINs to generate cartesian products of tables. Use ON,  
WHERE, and HAVING to filter the combinatorial explosion to only those  
rows that you want.)

And you are absolutely right: subqueries can (always? often?) be  
replaced by studlier joins. In my system, I’ve re-cast what was  
previously a four-level (!!) nested query with a “flat” join across  
seven tables. And it now runs like a bat out of hell.

I _STILL_ don’t know how to ask the ActiveRecord query system to alias a  
table in a join, as in:  
…  
INNER JOIN vertices AS v1 ON v1.id = edge.v1\_id  
INNER JOIN vertices AS v2 ON v2.id = edge.v2\_id  
…  
so I’m still using find\_by\_sql(). If someone can suggest a clean  
ActiveRecord syntax for this kind of query, I’m all ears.

- ff