Ordering on a many-to-many relationship with FuelPHP

Question

I've got a problem with sorting on a many-to-many relationship. What I'm trying to achieve seems like it should be fairly straightforward, but after a lot of banging my head against Fuel, I still can't get it to work.

(Incidentally, I realise more-or-less this question has been asked before, but since (a) at that time it wasn't possible to sort on a lazy-loading relationship, and (b) I've got a lot more detail of the exact problem I'm having, I thought it would be worth asking as a separate question...)

Here's the problem I'm having:

I have an "Item" model. Items can have children (which are also items), joined via a many-to-many relationship through an "items_items" table with a 'parent_id' and 'child_id' column. The "items_items" table also has a "sortorder" column, so that the order of the child items can be set.

Creating a separate model for this relationship seemed like overkill to me, so the order is updated by an observer that updates child items' sortorder when the parent is saved (via an "_event_after_save" method on Model_Item). This seems to work fine.

However, the problem I'm having is that I can get sort order working either with lazy loading or with eager loading, but not both. Whichever one works, the other throws Fuel errors, so the only way currently of getting both eager and lazy loading to work is to scrap the 'order_by' clause when eager-loading. Here are the three ways I've tried defining the 'children' relationship:

Approach #1

• This is the approach that the relevant page in FuelPHP's documentation suggests should work.

• No errors on eager or lazy load, but either way, order_by is not respected (child items are ordered by id, not by sortorder)

  'children' => array(
      'table_through'    => 'items_items',
      'key_through_from' => 'parent_id',
      'key_through_to'   => 'child_id',
      'model_to'         => 'Model_Item',
      'order_by' => array(
          'items_items.sortorder' => 'ASC'
      ),
  )
  

Approach #2

• Works as desired with eager loading

• Causes Fuel error with lazy loading ("Column not found: 1054 Unknown column 'items_items.sortorder' in 'order clause'") This seems to be because items_items is aliased to 't0_through' in the JOIN clause, but not in the ORDER BY clause.

  'children' => array(
      'table_through'    => 'items_items',
      'key_through_from' => 'parent_id',
      'key_through_to'   => 'child_id',
      'model_to'         => 'Model_Item',
      'conditions'            => array(
          'order_by' => array(
              'items_items.sortorder' => 'ASC'
          ),
      )
  )
  

Approach #3

• Works as desired with lazy loading

• Causes Fuel error with eager loading (effectively the reverse of take #2, above)

  'children' => array(
      'table_through'    => 'items_items',
      'key_through_from' => 'parent_id',
      'key_through_to'   => 'child_id',
      'model_to'         => 'Model_Item',
      'conditions'            => array(
          'order_by' => array(
              't0_through.sortorder' => 'ASC'
          ),
      )
  )
  

In a slightly desperate hack, I tried combining approaches 2 and 3 above by defining two separate relationships ('children' and 'child') - one for eager loading, the other for lazy loading, but it still breaks my app, because it makes the delete process throw similar errors. I could attempt to fix this, but I feel like that would just be piling hack upon hack. What I'd like instead is a solid way of ordering child items that works whether they were eager or lazy loaded. Fuel's docs suggest this should be doable (approach #1, above), but I just can't get that to work...

Any help would be very much appreciated!

Answer 1

I've finally managed to fix this issue. It turned out to be a problem with the current release of Fuel's ORM (version 1.7).

What worked for me was to update the ORM to a more recent development version (60cc4eb576 on the ORM's 1.8/develop branch), and then to set the 'order_by' on the many-to-many relation like this:

...
'conditions' => array(
    'order_by' => array(
        'items_items.sortorder' => 'ASC'
    ),
)
...

This is a combination of approaches 2 and 3 above, and not quite the syntax that the documentation currently suggests, but seems to work as expected / desired for both eager- and lazy-loading.

Answer 2

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.