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:[email protected]
http://www.dankohn.com/ tel:+1-415-233-1000