Rails - Jul 2006 - How bad is it to have 7 joins in my find_by_sql?

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 Kohn <mailto:dan@dankohn.com>
<http://www.dankohn.com/>  <tel:+1-415-233-1000>

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

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

-- 
Posted via http://www.ruby-forum.com/.

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 Kohn <mailto:dan@dankohn.com>
<http://www.dankohn.com/>  <tel:+1-415-233-1000>



On Jul 15, 2006, at 11:13 PM, Julian Gall wrote:
> 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
>
> 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
>
> -- 
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails

On Jul 15, 2006, at 11:48 pm, Dan Kohn 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