Improper join generation on associations
Reported by ash.gti | December 7th, 2008 @ 05:41 PM
Doing nested association conditions on a find results in an improper join statement.
my attacked file has references to the classes I am using
You can do this: User.all(User.posts.title => "Hi")
and it works just fine, it generates:
SELECT "users"."id", "users"."name" FROM "users" INNER JOIN "posts" ON ("users"."id" = "posts"."user_id") WHERE ("posts"."title" = 'Hi') ORDER BY "users"."id"
but if you try to reference a nested association like: User.all(User.posts.comments.from => "John")
it generates:
SELECT "users"."id", "users"."name" FROM "users" INNER JOIN "posts" ON ("users"."id" = "posts"."user_id") INNER JOIN "posts" ON ("posts"."id" = "comments"."post_id") WHERE ("comments"."from" = 'John') ORDER BY "users"."id"
Which is not joining the comments table and results in an improper SQL statement.
It should be:
SELECT "users"."id", "users"."name" FROM "users" INNER JOIN "posts" ON ("users"."id" = "posts"."user_id") INNER JOIN "posts" ON ("users"."id" = "posts"."user_id") INNER JOIN "comments" ON ("posts"."id" = "comments"."post_id WHERE ("comments"."from" = 'John') ORDER BY "users"."id"
Also note that the first JOIN has the wrong conditions. They were not associating the posts to the users.
Comments and changes to this ticket
-
Nathan December 10th, 2008 @ 04:28 PM
I can confirm this with dm-core 0.9.8
I have another example of how this happens with :through associations as well.
-
Dan Kubb (dkubb) January 8th, 2009 @ 05:13 AM
- State changed from “unconfirmed” to “accepted”
- Assigned user set to “Dan Kubb (dkubb)”
-
ash.gti January 9th, 2009 @ 02:34 PM
I checked the dkubb/dm-core to test if this is fixed and I found an error with the way its making the Join statements, but luckily, and the specs still work.
In dm-core/lib/dm-core/adapters/data_objects_adapter.rb on line 292 I moved the Associations::OneToMany::Relationship to 294.
Here is a patch for it, I also added a spec for testing this, don't know if its formatted exactly right but I'll be happy to update it if you'd like.
-
Jonathan Stott (namelessjon) February 22nd, 2009 @ 12:11 PM
- Tag changed from “associations, join, nested” to “associations, join, nested, one_file_test”
- State changed from “accepted” to “resolved”
This doesn't appear to be an issue as of 0.10.0 (see attached test)
I can't speak for the precise queries generated, but the results seem to be correct.
Please Sign in or create a free account to add a new ticket.
With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.
Create new ticket
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »
ash.gti
Dan Kubb (dkubb)