# How bad is it to have 7 joins in my find\_by\_sql?

**URL:** <https://www.ruby-forum.com/t/how-bad-is-it-to-have-7-joins-in-my-find-by-sql/98554>\
**Category:** Rails\
**Created:** [July 16, 2006, 12:48am UTC](https://www.ruby-forum.com/t/how-bad-is-it-to-have-7-joins-in-my-find-by-sql/98554 "2006-07-16T00:48:38Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![dankitania](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/e9a140/32.png) [@dankitania](https://www.ruby-forum.com/u/dankitania)\
**Post date:** [July 16, 2006, 12:48am UTC](https://www.ruby-forum.com/t/how-bad-is-it-to-have-7-joins-in-my-find-by-sql/98554/1 "2006-07-16T00:48:38Z")

</div>

My question is whether there’s a more idiomatic rails way of  
structuring this query or of redoing the underlying models. First,  
the ugly find\_by\_sql code, which is the method to generate an atom feed:

def atom  
items\_per\_feed = 15  
sql\_query = “SELECT activities.\*, users.real\_name AS real\_name,  
accounts.last\_scraped\_at AS last\_scraped\_at,  
accounts.total\_miles as account\_miles,  
origins.name AS origin\_name, origins.code AS origin\_code,  
destinations.name  
AS destination\_name, destinations.code AS destination\_code,  
airlines.name  
AS airline\_name, airlines.code AS airline\_code, programs.name  
AS program\_name,  
membership\_levels.name AS membership\_level FROM activities  
LEFT OUTER JOIN accounts ON activities.account\_id = accounts.id  
LEFT OUTER JOIN  
users on accounts.user\_id = users.id LEFT OUTER JOIN  
airports AS origins ON activities.origin\_id = origins.id LEFT  
OUTER JOIN airports  
AS destinations ON activities.destination\_id = destinations.id  
LEFT OUTER JOIN  
airlines ON activities.airline\_id = airlines.id LEFT OUTER JOIN  
programs ON  
accounts.program\_id = programs.id LEFT OUTER JOIN  
membership\_levels ON  
accounts.membership\_level\_id = membership\_levels.id  
WHERE accounts.user\_id = #{@session[:user].id}  
ORDER BY activities.date DESC LIMIT #{items\_per\_feed}”  
@activities = Activity.find\_by\_sql(sql\_query)  
end

Now, here’s a stripped down version of the models, which are for  
keeping track of frequent flyer information:

class User \< ActiveRecord::Base  
has\_many :accounts  
end

class Account \< ActiveRecord::Base  
belongs\_to :program  
belongs\_to :user  
belongs\_to :membership\_level  
has\_many :activities  
end

class Activity \< ActiveRecord::Base  
belongs\_to :origin, :class\_name =\> “Airport”, :foreign\_key =\>  
“origin\_id”  
belongs\_to :destination, :class\_name =\> “Airport”, :foreign\_key =\>  
“destination\_id”  
belongs\_to :account  
belongs\_to :airline  
end

class Airport \< ActiveRecord::Base  
has\_many :activities, :foreign\_key =\> “origin\_id”  
has\_many :activities, :foreign\_key =\> “destination\_id”  
end

class Program \< ActiveRecord::Base  
belongs\_to :airline  
has\_many :membership\_levels  
has\_many :accounts  
end

class MembershipLevel \< ActiveRecord::Base  
belongs\_to :program  
has\_many :accounts  
end

class Airline \< ActiveRecord::Base  
has\_many :activities  
has\_many :programs  
end

A few things of note. One of the complexities is that I’m joining  
the airports table twice, once as origin and once as destination. I  
don’t think there’s any support for this using the regular find  
method. Separately, I designed these models prior to the  
availability of has many :through, but I now can’t decide whether and  
how I should make use of that feature.

Obviously, I’ve gone through the trouble of making the above work.  
However, I’d like any opinions on whether there’s a better way to  
accomplish the same outcome. Also, will this many joins bring my  
database to its knees under load? Thanks in advance for your thoughts.

```
        - dan

```

–  
Dan K. [mailto:removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
[http://www.dankohn.com/](http://www.dankohn.com/)tel:+1-415-233-1000

---

<div class="post-metadata">

**Author:** ![dankitania](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/e9a140/32.png) [@dankitania](https://www.ruby-forum.com/u/dankitania)\
**Post date:** [July 16, 2006, 8:13am UTC](https://www.ruby-forum.com/t/how-bad-is-it-to-have-7-joins-in-my-find-by-sql/98554/2 "2006-07-16T08:13:36Z")

</div>

If you’re using a database version that supports it, you could use a  
stored procedure. Put the whole select statement with all its joins into  
the stored procedure and call it with find\_by\_sql. e.g. See  
[http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html](http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html)

This is perhaps not idiomatic for Rails but it has the advantage that  
the database engine can optimise the query once. Your Rails app then  
deals with a simple database entity called atom.

Regarding performance, with the correct indexes on the key fields, it  
should be as fast as the complexity of your request allows. If there is  
really a problem, you could run the query for all users periodically and  
store the results in a temporary table for lookup by user id as  
required. But that is definitely not idiomatic for SQL!

Julian

---

<div class="post-metadata">

**Author:** ![dankitania](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/e9a140/32.png) [@dankitania](https://www.ruby-forum.com/u/dankitania)\
**Post date:** [July 16, 2006, 7:37pm UTC](https://www.ruby-forum.com/t/how-bad-is-it-to-have-7-joins-in-my-find-by-sql/98554/3 "2006-07-16T19:37:53Z")

</div>

Thanks. I’m planning on doing action caching on the atom feed and  
using a sweeper, which should limit the queries to only be done when  
there’s new info for that user.

```
        - dan

```

–  
Dan K. [mailto:removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
[http://www.dankohn.com/](http://www.dankohn.com/)tel:+1-415-233-1000

---

<div class="post-metadata">

**Author:** ![dankitania](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/e9a140/32.png) [@dankitania](https://www.ruby-forum.com/u/dankitania)\
**Post date:** [July 16, 2006, 9:14pm UTC](https://www.ruby-forum.com/t/how-bad-is-it-to-have-7-joins-in-my-find-by-sql/98554/4 "2006-07-16T21:14:12Z")

</div>

On Jul 15, 2006, at 11:48 pm, Dan K. wrote:

> Obviously, I’ve gone through the trouble of making the above work.  
> However, I’d like any opinions on whether there’s a better way to  
> accomplish the same outcome. Also, will this many joins bring my  
> database to its knees under load? Thanks in advance for your  
> thoughts.

Hi Dan

The number of joins in your query doesn’t bother me too much, but…  
do they really all need to be left joins? I’ve found that database  
servers have a much harder time optimising queries with left joins  
than inner joins. If you can be sure that every row in table A has a  
corresponding row in table B, then you should always use an inner  
join. If it might not, double check why not, and also if it is  
actually meaningful to bring back the part of the result set for A  
without entries for B. Obviously the performance depends entirely on  
what system you are using (if you didn’t write it by hand, then  
judging by the SQL formatting I’m guessing it came from SQL Server’s  
Enterprise Manager!!!)

Ashley