Combining two joins (via named_scopes) fails if traversing classes are STI subclasses

rubyonrails-talk
1

I refactored a Location class into a STI locations with PostalAddress < Location Place < Location

I also have Customer < Company into a STI companies

Before that, I had the following named_scope:

class RequestedItem < ActiveRecord::Base   belongs_to :item_variant

  named_scope :for_country, lambda { |countries| {       :joins => {:order_request => {:customer => :postal_address}},   }} end #customer is a sti subclass of company and postal_address a sti subclass of location

When I execute this scope after this refactoring:

RequestedItem.for_country('VU')

RequestedItem Load (0.7ms) SELECT "requested_items".* FROM "requested_items" INNER JOIN "order_requests" ON "order_requests".id = "requested_items".order_request_id INNER JOIN "companies" ON "companies".id = "order_requests".customer_id AND ("companies"."type" = 'Customer' ) INNER JOIN "locations" ON "locations".id = "companies".postal_address_id AND ("locations"."type" = 'PostalAddress' )

The scope returns a proper SQL request but as soon as I join it with something else, Rails output a SQL "AND" at the wrong place:

RequestedItem.for_country('VU').all(:joins => :item_variant)

SQLite3::SQLException: near "=": syntax error: SELECT DISTINCT(locations.country) FROM "requested_items" INNER JOIN "order_requests" ON "order_requests".id = "requested_items".order_request_id INNER JOIN "companies" ON "companies".id = "order_requests".customer_id AND ("companies"."type" = 'Customer' ) INNER JOIN "locations" ON "companies".postal_address_id AND = "locations".id ("locations"."type" = 'PostalAddress' ) WHERE (order_requests.workflow_state = 'open' AND requested_items.balance_cache <> 0)

The last join is rotten here: INNER JOIN "locations" ON "companies".postal_address_id AND = "locations".id ("locations"."type" = 'PostalAddress' ) the AND should be AFTER '= "locations".id

I really hope someone can help me to correct that because I have the scary feeling this is a Rails bug...

Cheers, Gam.

PS: Running Rails 2.3.10/Ruby 1.8.6/ Mac OS X 10.5.8

2

It looks like an issue with the SQL generation part, but it's going to be impossible to debug without some sort of test case. You're probably going to get the most help if you can reduce it to a minimal app + tests that can reproduce the issue and post that over on rails.lighthouseapp.com. I suspect, based on where the error happens, that it's a missing corner case in JoinDependency#association_join (line 2062 of associations.rb), but it's hard to tell.

--Matt Jones

3

It looks like an issue with the SQL generation part, but it's going to be impossible to debug without some sort of test case. You're probably going to get the most help if you can reduce it to a minimal app + tests that can reproduce the issue and post that over on rails.lighthouseapp.com. I suspect, based on where the error happens, that it's a missing corner case in JoinDependency#association_join (line 2062 of associations.rb), but it's hard to tell.

--Matt Jones

Hi Matt, Indeed, I think that's the case. I'm going to do a Rails 3 app to see if it's still exhibit the problem. Thanks for confirming my suspicions.

Regards, Gam.