Rails - Jan 2011 - Combining two joins (via named_scopes) fails if traversing classes are STI subclasses

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


-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Jan 13, 11:58 am, gamov
<gamaud...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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...
>
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

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

> 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.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.