Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Ordering many_to_many relation
  • Hi,

    Is it possible to add an ordering field directly inside a many_to_many (or a has_many) relation which is not defined in the related objects ?
    For example : I need to link articles to categories in a many to many relationship and I would like to order articles inside categories. Of course, an article may be in multiple categories, so I can't simply add a field in the article table (which is possible in a has_many relation).

    It would be useful to have a native way to simply reorder related objects.

    I worked on an ORM years ago and it took me weeks to implement partially that functionality, specially because of a cache feature, but I don't remember I saw it elsewhere.

  • You can order on related fields without problems, just prefix the fieldname with the relation name:

    $result = Model_Category::query()->related('article')->order_by('article.author', 'ASC')->get();

    Or do you mean you want to order on a field in the join table (which is possible too, but a bit more complex)?

    And if you always want the same order, you can also define an order_by in the conditions of the relation definition in the model.
  • I was thinking of an order on a field in the join table, I am happy to read that this is possible (even complex).
  • Easier than i thought, a permanent solution was implemented about 9 months ago.

    See the docs, http://docs.fuelphp.com/packages/orm/relations/many_many.html, "Ordering on a column in the through table"...
  • Excellent news ! Now, is it possible to change the ordering using the ORM or do I need to develop specific methods ?
  • You can just use the normal order_by(), as in my first reply, but instead of prefixing the column name with a relation name, you prefix it with the name of the through_table as defined in your many-to-many relation definition.

    Like it's documented, see the link in my previous reply.
  • I'm sorry I was not enough precise or this wasn't good english (I'm french...), or I am nutt (I'm French ^^) I was talking of updating the ordering of effectively related objects.

    For example :

    Suppose Model_Category::$articles gives me an array of Model_Article associated to Model_Category by a many to many relation using an ordering column `position` in the (through) table `category_has_article` (with fields category_id, article_id, position).

    Suppose $category = Model_Category::find(17) and $articles = $category->articles is an array of two objects $article_48 (article_id=48 with position=1) and $article_752 (article_id=752 with position=2). If I want to reorder them, i.e. change the value of the field `position` in the table `category_has_article`, so that I have $article_752 (with position=1) at first and $article_48 (with position=2) in second position.

    I can do queries like : UPDATE `category_has_article` SET `position`=... WHERE ... but is there another way native in the ORM ?
  • You can only do that if you create a model for your join table, it hasn't really been designed for that.

    Either:
    - you have a many-to-many with a through table that only contains both foreign keys

    Or:
    - you have a one-to-many relation between both tables using a full model for the join table

    Or
    - you do both, and access the relation that makes the most sense.

    Note that if you do this, there is no connection between the two. So if you access the many-to-many, the one-to-many's to the through table will not be automatically populated. Also, if you delete a many-to-many child record, all join table records with that foreign key will be deleted. Both the loaded objects in the one-to-many will still exist, but will no longer be valid (a save will give an exception).

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion