Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

This project is archived and is in readonly mode.

Constructed SQL is wrong for multiple :joins based on has_many with :conditions

#2908

The generated INNER JOIN statement is wrong if there are multiple has_many with :conditions and all are joined. See this example:

class Agreement < ActiveRecord::Base
  has_many :vendor_involvements,     :class_name => 'Involvement', :as => :object, :conditions => { :role => 'vendor' }
  has_many :contractor_involvements, :class_name => 'Involvement', :as => :object, :conditions => { :role => 'contractor' }
end

class Involvement < ActiveRecord::Base
  belongs_to :object, :polymorphic => true
end

Agreement.find :all, :joins => [ :vendor_involvements, :contractor_involvements ]

This SQL query is generated:

SELECT `agreements`.* 
FROM `agreements` 
INNER JOIN `involvements` 
      ON `involvements`.object_id = `agreements`.id 
      AND `involvements`.object_type = 'Agreement'
      AND `involvements`.`role` = 'vendor' 
INNER JOIN `involvements` contractor_involvements_agreements 
      ON `contractor_involvements_agreements`.object_id = `agreements`.id 
      AND `contractor_involvements_agreements`.object_type = 'Agreement'
      AND `involvements`.`role` = 'contractor'

This is wrong, because in the last line it should be

... AND contractor_involvements_agreements.role = 'contractor'
instead of
... AND involvements.role = 'contractor'

There should be used the alias, not the table name.

Reported by Georg Ledermann · July 14th, 2009 @ 10:11 AM

State: resolved
Milestone: 2.x
Assigned to: nobody
Importance: none

Activity

  1. Georg Ledermann
    Georg Ledermann
    • Tag changed from 2.3.2, conditions, has_many, join, joins, sql to 2.3.2, 2.3.3, activerecord, conditions, has_many, join, joins, sql

    Still occurs in Rails 2.3.3

    July 20th, 2009 @ 09:15 PM

  2. Georg Ledermann
    Georg Ledermann
    • Tag changed from 2.3.2, 2.3.3, activerecord, conditions, has_many, join, joins, sql to 2.3.2, activerecord, conditions, has_many, join, joins, sql

    Please ignore my last comment - it's not true. The bug IS fixed in Rails 2.3.3. Thanks!

    http://github.com/rails/rails/commit/489abfd3b23f3c4b3de86aeb3bde39...

    July 23rd, 2009 @ 05:41 PM

  3. Michael Koziarski
    Michael Koziarski
    • State changed from new to resolved

    August 3rd, 2009 @ 06:06 AM

  4. bingbing