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.

joins() on has_one through generates SQL query with "IS NULL"

#6085

using ruby 1.9.2, rails 3.0.3

Post
belongs_to :user_project
has_one :project, :through => :user_project

UserProject
belongs_to :project
has_many :posts

Project
has_many :user_projects
has_many :posts, :through => :user_projects

here is the problem:

irb> Post.joins(:project)
=> []

here is the generated query:
SELECT posts.* FROM posts INNER JOIN user_projects ON posts.id IS NULL INNER JOIN projects ON projects.id = user_projects.project_id

where is "IS NULL" coming from?

Reported by avioing · November 29th, 2010 @ 09:14 PM

State: duplicate
Milestone: none
Assigned to: nobody
Importance: Low

Activity

  1. Joeri Samson
    Joeri Samson

    I have the same problem (also using Rails 3.0.3, but using JRuby), additionally if I make the join 1 level deeper I get an INNER JOIN table_name ON 0.

    class FociSample < ActiveRecord::Base
      belongs_to :foci_group
      has_one    :source_well, :through => :foci_group
    end
    
    class FociGroup < ActiveRecord::Base
      belongs_to :source_well, :foreign_key => "source_id", :class_name => "CultureWell"
    end
    
    class CultureWell < ActiveRecord::Base
      belongs_to :culture_plate
    end
    

    When trying

    FociSample.joins(:source_well => :culture_plate)
    
    the generated query is:
    SELECT foci_samples.* FROM foci_samples INNER JOIN foci_groups ON foci_samples.id IS NULL INNER JOIN culture_wells ON 0 INNER JOIN culture_plates ON culture_plates.id = culture_wells.culture_plate_id
    

    Adding an additional level

    class CulturePlate < ActiveRecord::Base
      belongs_to :culture_batch
    end


    FociSample.joins(:source_well => {:culture_plate => :culture_batch})
    results in this query:
    SELECT foci_samples.* FROM foci_samples INNER JOIN foci_groups ON foci_samples.id IS NULL INNER JOIN culture_wells ON 0 INNER JOIN culture_plates ON culture_plates.id = culture_wells.culture_plate_id INNER JOIN culture_batches ON culture_batches.id = culture_plates.culture_batch_id
    
    where the join condition for the extra dependency makes sense again.

    December 15th, 2010 @ 05:07 PM

  2. Jon Leighton
    Jon Leighton
    • State changed from new to duplicate
    • Importance changed from to Low

    Hi there,

    Thanks for the bug report.

    This is a duplicate of #2801 incorrect sql from has_many :through , which is fixed in master.

    Jon

    December 21st, 2010 @ 08:16 PM