Is there a bug with .where on polymorphic has_one associations?

rubyonrails-talk
,
1

I’m usually not one to jump to “the library must have a bug” when my code breaks, but after talking over some behavior I’ve been seeing with a coworker I’m not so sure.

The app is very simple. It’s just a (half baked) bookshelf manager for those IKEA grid bookshelves.

The issue has to do with querying the Book model. Book has a polymorphic has_one relationship with Item via the placeable polymorphic interface. During development, I accidentally tried to filter Books by an Item id. I did:

Book.where(item: 14)

which generated

SELECT "books".* FROM "books" WHERE "books"."id" = $1  [["id", 14]]

This does not do what it should. It loads books with the id 14, not books associated with items with id 14. Basically I expected it to generate the same SQL as this (which is what I meant to write):

Book.where(item: Item.find_by(id: 14))

which generates

SELECT "items".* FROM "items" WHERE "items"."id" = $1 ORDER BY "items"."index" ASC LIMIT $2  [["id", 14], ["LIMIT", 1]]
SELECT "books".* FROM "books" WHERE "books"."id" = $1  [["id", 11]]

So, my question is, obviously my first query was nonstandard/nonsense, but with that in mind, shouldn’t it have either generated the same SQL as the intended query or errored out? Isn’t the generated SQL just wrong?

For those interested in messing with my repo, here it is: GitHub - AHBruns/buk. It uses Figaro for all configuration. You’ll need an application.yml like this to get things working:

development:
  # db
  database_name: "buk_dev"
  database_username: ""
  database_password: ""
  # misc
  jwt_secret: "development_jwt_secret"
  google_books_api_key: "<you gotta provide this>"

test:
  # db
  database_name: "buk_test"
  database_username: ""
  database_password: ""
  # misc
  jwt_secret: "testing_jwt_secret"
  google_books_api_key: "<you gotta provide this>"

production:
  # todo
1 Like
2

Wow, I think you might have found something here. I have an app with both has_one and has_many relationships, so I tested your theory.

In my app, Project has many Message:

[1] pry(main)> Project.where(message: 1).to_sql
=> "SELECT \"projects\".* FROM \"projects\" WHERE \"projects\".\"message\" = 1"

and Order has one Payment:

[2] pry(main)> Order.where(payment: 1).to_sql
=> "SELECT \"orders\".* FROM \"orders\" WHERE \"orders\".\"id\" = 1"

The I think what you are actually looking for might be Book.where(item_id: 14), but the has one and has many definitely do seem to generate a different SQL output. I’m very curious why that is.

I think this is probably worth opening an issue (and maybe a PR!) on the rails repo. You might find that an issue for this already exists, too.

Nice find!

3

Interesting, and your associations are regular or polymorphic? Regardless, I’ll go ahead and make an issue. I’m not familiar with the AR codebase, but I’ll do some reading and see if I can PR a fix myself.

4

So, after some digging, I found this issue: has_one where statement generates incorrect sql · Issue #37531 · rails/rails · GitHub It appears to be about the same bug. It’s been open, but stale, for multiple years. So, I don’t have a ton of hope it will be addressed any time soon, but I’ll still see if I can make some headway on writing a fix myself.

1 Like
5

Nice find. My relationships are not polymorphic, so it seems the issue might not be specific to polymorphic relationships.

Good luck on that PR, I’d love to see it if you feel like posting a link after you make it.