Sergio Campamá
2014-Feb-01 04:32 UTC
mysql2 generating bad syntax on joins with polymorphic join table
Hi,
I don't know if this is an allowed design in rails, but it seems to me that
it should be. I'll first describe the models and then explain the (what I
feel is, but want confirmation) error. An example repo with instructions on
how to reproduce this can be found here:
https://github.com/sergiocampama/multiple_join_bug , I am using rails
4.0.2, (actual versions of all the gems can be found in Gemfile.lock).
I have a model called Center, and 2 models called Left and Right. Center
has_many :lefts, and has_many :rights, both through a join model called
Link, which belongs_to :center and belongs_to :linkable, polymorphic: true.
Center also has scopes to find centers with associated lefts, rights and a
combination, called :with_left(left), :with_right(right) and
:with_left_and_right(left, right), which join the required links and use a
where(rights: {id: right.id}) or the corresponding where clause.
The problem arises when I have this query:
left.centers.with_right(right).count
which generate a StatementInvalid error in mysql2 (sqlite3 silently fails
and I haven't tested pg). The generated SQL is this:
SELECT COUNT(*) FROM `centers` INNER JOIN `links`
`right_links_centers_join` ON `right_links_centers_join`.`center_id`
`centers`.`id` AND `right_links_centers_join`.`linkable_type` = 'Right'
AND
`links`.`linkable_type` = 'Right' INNER JOIN `rights` ON `rights`.`id`
`right_links_centers_join`.`linkable_id` INNER JOIN `links` ON
`centers`.`id` = `links`.`center_id` WHERE `links`.`linkable_id` = 36 AND
`links`.`linkable_type` = 'Left' AND `links`.`linkable_type` =
'Left' AND
`rights`.`id` = 36
As you can see, the first reference to the links table gets aliased
to right_links_centers_join, and the second reference doesn't get an alias
(because it's not needed). The highlighted parts are extras that
shouldn't
be there. The first one generates the error as the links table hasn't been
referenced by that name, and the second one is a repeat of the previous
condition (in blue). If the second reference to links had been aliased, it
would also generate an error (not seen in the example repo but in another
case).
The red highlights, besides generating an error, are not needed, as the
blue highlights cover the those conditions.
At first I thought that it could be from the source_type option in the
has_many :lefts options in Control, but because the join table is
polymorphic, rails complains that the source_type needs to be explicit.
Is this a bug? Or am I using the has_many in a wrong way? Even if I'm
wrong, I think it should throw an error before generating a wrong SQL query.
Best regards,
--------------------------------------
Sergio Campamá
sergiocampama@gmail.com
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to rubyonrails-core+unsubscribe@googlegroups.com.
To post to this group, send email to rubyonrails-core@googlegroups.com.
Visit this group at http://groups.google.com/group/rubyonrails-core.
For more options, visit https://groups.google.com/groups/opt_out.