ActiveRecord: has_many through with foreign scope misses joining the table and generates wrong sql #14110

Open
doits opened this Issue Feb 19, 2014 · 8 comments

Comments

Projects
None yet
8 participants

doits commented Feb 19, 2014

See gist https://gist.github.com/doits/60b862bb45855b506ad9 for test template.

Basically when going from model Person over Houses to Colors, where Houses is filtered by a scope damaged that joins Conditions, the generated SQL misses to join the table Conditions.

The generated SQL for getting person.damaged_houses is correctly joining the tables:

  SELECT "houses".* FROM "houses" 
    INNER JOIN "conditions_houses" ON "conditions_houses"."house_id" = "houses"."id"
    INNER JOIN "conditions" ON "conditions"."id" = "conditions_houses"."condition_id"
    WHERE "houses"."person_id" = ? AND "conditions"."condition" = 'damaged'

But when selecting person.damaged_colors, the join is missed, only the condition is inserted:

SQLite3::SQLException: no such column: conditions.condition:
  SELECT "colors".* FROM "colors"
    INNER JOIN "houses" ON "colors"."house_id" = "houses"."id"
    WHERE "houses"."person_id" = ? AND "conditions"."condition" = 'damaged'

If I see it correctly, simply the join is missing, so the query should be:

SELECT "colors".* FROM "colors"
  INNER JOIN "houses" ON "colors"."house_id" = "houses"."id"
  INNER JOIN "conditions_houses" ON "conditions_houses"."house_id" = "houses"."id"
  INNER JOIN "conditions" ON "conditions"."id" = "conditions_houses"."condition_id"
  WHERE "houses"."person_id" = ? AND "conditions"."condition" = 'damaged'

Is this an ActiveRecord bug or am I doing it wrong?

This issue has been automatically marked as stale because it has not been commented on for at least
three months.

The resources of the Rails team are limited, and so we are asking for your help.

If you can still reproduce this error on the 4-1-stable, 4-0-stable branches or on master,
please reply with all of the information you have about it in order to keep the issue open.

Thank you for all your contributions.

doits added the stale label May 27, 2014

doits commented May 27, 2014

Issue still happening on 4.1.1, updated gist to reflect new version.

Owner

rafaelfranca commented May 27, 2014

Thank you so much. Removed from stale tag.

rafaelfranca removed the stale label May 27, 2014

rails-bot added the stale label Nov 19, 2014

This issue has been automatically marked as stale because it has not been commented on for at least
three months.

The resources of the Rails team are limited, and so we are asking for your help.

If you can still reproduce this error on the 4-1-stable, 4-0-stable branches or on master,
please reply with all of the information you have about it in order to keep the issue open.

Thank you for all your contributions.

PlugIN73 commented May 8, 2015

Hi all. Happened to me:

 has_many :course_members, -> (object) { joins(:course).where(courses: {state: :approved, locale: object.locale}) }, class_name: 'Course::Member'
 has_many :lesson_members, through: :course_members

: SELECT "lesson_members".* FROM "lesson_members" INNER JOIN "course_members" ON 
"lesson_members"."course_member_id" = "course_members"."id" 
WHERE "course_members"."user_id" = $1 AND "courses"."locale" = $2 AND "courses"."state" = 'approved'
Contributor

miguelgraz commented Jun 12, 2015

Confirming that it still happens on current master, I'll try to suggest a solution if no one is already working on this.

Contributor

sevaorlov commented May 7, 2016

might be related to #22538

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment