named_scope + average is causing the table to be specified more then once in the sql query run on postgresql

Question

I have a named scopes like so...

  named_scope :gender, lambda { |gender| { :joins => {:survey_session => :profile }, :conditions => { :survey_sessions => { :profiles => { :gender => gender } } } } }

and when I call it everything works fine.

I also have this average method I call...

Answer.average(:rating, :include => {:survey_session => :profile}, :group => "profiles.career")

which also works fine if I call it like that.

However if I were to call it like so...

Answer.gender('m').average(:rating, :include => {:survey_session => :profile}, :group => "profiles.career")

I get...

ActiveRecord::StatementInvalid: PGError: ERROR: table name "profiles" specified more than once : SELECT avg("answers".rating) AS avg_rating, profiles.career AS profiles_career FROM "answers" LEFT OUTER JOIN "survey_sessions" survey_sessions_answers ON "survey_sessions_answers".id = "answers".survey_session_id LEFT OUTER JOIN "profiles" ON "profiles".id = "survey_sessions_answers".profile_id INNER JOIN "survey_sessions" ON "survey_sessions".id = "answers".survey_session_id INNER JOIN "profiles" ON "profiles".id = "survey_sessions".profile_id WHERE ("profiles"."gender" = E'm') GROUP BY profiles.career

Which is a little hard to read but says I'm including the table profiles twice.

If I were to just remove the include from average it works but it isn't really practical because average is actually being called inside a method which gets passed the scoped. So there is some times gender or average might get called with out each other and if either was missing the profile include it wouldn't work.

So either I need to know how to fix this apparent bug in Rails or figure out a way to know what scopes were applied to a ActiveRecord::NamedScope::Scope object so that I could check to see if they have been applied and if not add the include for average.

Answer 1

Looks like ActiveRecord is generating some bad SQL:

SELECT avg("answers".rating) AS avg_rating,
       profiles.career AS profiles_career
FROM "answers"
     LEFT OUTER JOIN "survey_sessions" survey_sessions_answers
                    ON "survey_sessions_answers".id = "answers".survey_session_id
     LEFT OUTER JOIN "profiles"
                     ON "profiles".id = "survey_sessions_answers".profile_id
     INNER JOIN "survey_sessions"
                ON "survey_sessions".id = "answers".survey_session_id
     INNER JOIN "profiles"
                ON "profiles".id = "survey_sessions".profile_id
WHERE ("profiles"."gender" = E'm')
GROUP BY profiles.career

Presumably it's generated the left joins as part of getting the projected property, and the inner joins as part of getting the criteria: this wouldn't be invalid (just inefficient) if it assigned aliases to those tables, but it doesn't. Is there a way to specify an alias name from your app?