Querying via Many-to-Many relationships uses bad key?

[Adam Medveczky]

Hi!

If I have 2 models (Sentence, Word), which have a Many-to-Many
relationship, and Word has a field 'word', the following query:

Sentence.all(:words => { :word => "Hello" })

Tries to match "sentences.id" to "words.id" (live example at
http://pastebin.ca/2097242 ) :

SELECT "id", "text", "book_id"
FROM "sentences"
WHERE "id" IN
(SELECT "words"."id" # Query result will be a single words.id,
which will be used as sentence id
FROM "words"
INNER JOIN "sentence_words" ON "words"."id" =
"sentence_words"."word_id"
INNER JOIN "sentences" ON "sentence_words"."sentence_id" =
"sentences"."id"
WHERE "words"."word" = 'Hello')
ORDER BY "id"

Note that I haven't even heard of DataMapper until today, so I might
doing something bad, but this seems to be definiately wrong (the inner
query should be SELECT "sentences"."id" FROM "words" INNER JOIN...).

Thanks!

Adam

[Chris Corbyn]

Ugh, this looks pretty horrible. I think we ran into a similar bug where DataObjectsAdapter's join logic was using back-to-front keys in many-to-many associations. I remember chiming into the #datamapper channel at the time but don't recall what the outcome was (I think nobody was around).

The logic in DataObjectsAdapter is definitely back-to-front, but changing it seems to break other things. From memory it was changed to be back-to-front at some point, presumably to "fix" some other obscure bug.

Hopefully somebody takes a look at this, as we'll certainly also run into it.

In the interim, you may wish to try using an explicit join model (i.e. has n, :sentence_words on each model, then has n, :sentences, :through => :sentence_words).

> --
> You received this message because you are subscribed to the Google Groups "DataMapper" group.
> To post to this group, send email to datam...@googlegroups.com.
> To unsubscribe from this group, send email to datamapper+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/datamapper?hl=en.
>

[Adam Medveczky]

I have opened an issue for this: https://github.com/datamapper/dm-do-adapter/issues/11

Unfortunately I'm just learning ruby as well, DM internals are a bit
of mistery for me, so I did not get far.

Sentence_Words is generated automatically even if ':through =>
Resource' is specified, so without modificating the original code this
workaround is OK:

Sentence.all(:sentence_words => { :word_id => Word.first(:fields =>
[:id], :word => "Hello").id} )

[Adam Medveczky]

I mean I did not get far trying to fix the issue myself