I'm having a bit of a headache trying to get sorting on a many-to-many relationship to work using Fuel's ORM, despite (as far as I can see) following what the documentation suggests. I'm wondering if I'm hitting a possible bug in the ORM? ...or perhaps I'm just doing something obviously wrong. Either way, I'm quite stuck, and any help would be very much appreciated. I've posted the question on Stack Overflow, so rather than duplicate it here, here's a link:
From the ORM's point of view, the "through_table" does not exist. It is only defined so it can construct the SQL needed to make the relation work. That also means that additional fields in the "through_table" are not supported, it can only contain key values.
If you want attributes (additional columns) in the through table, it becomes a standard table in your database, for which a model is required. Your many-2-many relation then breaks down into two one-2-many relations.
Note that both can exist at the same time, so you can still use the many-to-many if you don't need to query or use columns in the through table.
Thanks for the quick response, Harro - very much appreciated.
I get what you're saying, I think, but in that case I'm confused by the docs, which as far as I can see seem to be saying otherwise (see the "Ordering on a column in the through table" example on the 'Many-to-many' page). That seems to be the exact thing I'm trying to do, but doesn't seem to work (and doesn't seem to fit with what you're saying is possible). Has the ORM changed since that page was written, or something? Is there a version of the ORM where that does/did work?
I'm also still puzzled by the fact that the ORM apparently can and does respect conditions on the through table - just apparently not in a compatible way for both eager and lazy loading... The app I'm working on has actually had sortorder working fine for a while now, but was only using lazy-loading (via the slightly hacky approach #3, outlined above). It was when I recently introduced eager-loading in a few places as part of improving an API's response times that I ran into this problem.
...I guess what I'm asking here is just for confirmation / clarification about what it says on the "many-to-many" page, because I feel like what you said was pretty clear, and what the page says is pretty clear, but I'm not totally clear how they fit together!
Reading it again, and checking the source, it seems that this was introduced early 2013 as a side effect of another change (https://github.com/fuel/orm/issues/235). By me none the less.
In that ticket I asked to warn us if the change had side effects, but we never got a response.
Can you open an issue for this problem, and refer to this thread? Also, can you check the SQL of the lazy load queries that fail? Is your through table order by present at all?
Ah, interesting. Ok, I've just done as you suggest, and opened a ticket on Github. I've referred to this thread and the Stack Overflow question, and given some sample SQL, as requested. Let me know (here or over on Github) if there's any more info I can provide. Thanks again for your help with this!
Donate
