This project is archived and is in readonly mode.

#2908 ✓resolved
Georg Ledermann

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

Reported by Georg Ledermann | July 14th, 2009 @ 10:11 AM | in 2.x

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' }

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

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.

Comments and changes to this ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href=""></a>

People watching this ticket

Referenced by