Storing sort order for items held in an HABTM association - CakePHP

Question

In an ActiveRecord (CakePHP flavored) setup I have a HasAndBelongsToMany association setup on Videos and Bins: A Bin can store n references to Videos, and Videos can belong to n Bins.

I need to be able to manually set and store the display order of the Videos within a particular Bin (so the client can have his Videos in a particular order within the Bin.) My initial thought is to create a field in Bin that stores an array of Video IDs in the order they need to appear. Is this the most efficient way to handle this?

If so, when I then get the HABTM query result, what is the cleanest/most efficient way to re-order the returned query to match the sorted array of ID's?

The Videos associated with a Bin are returned as an array:

[Video] => Array
            (
                [0] => Array
                    (
                        [id] => 49b2de95-0338-48b9-bc88-01ba35664358
                        ...

                    )

                [1] => Array
                    (
                        [id] => 49b1ccad-b770-410e-be46-03a035664358
                        ...
            )

Or is there a better way to achieve what I'm trying to do without using HABTM associations?

Thanks in advance -

Answer 1

What to do when HABTM becomes complicated?

By default when saving a HasAndBelongsToMany relationship, Cake will delete all rows on the join table before saving new ones. For example if you have a Club that has 10 Children associated. You then update the Club with 2 children. The Club will only have 2 Children, not 12.

Also note that if you want to add more fields to the join (when it was created or meta information) this is possible with HABTM join tables, but it is important to understand that you have an easy option.

HasAndBelongsToMany between two models is in reality shorthand for three models associated through both a hasMany and a belongsTo association.

Consider this example:

Child hasAndBelongsToMany Club

Another way to look at this is adding a Membership model

Child hasMany Membership
Membership belongsTo Child, 
ClubClub hasMany Membership.

These two examples are almost the exact same. They use the same amount and named fields in the database and the same amount of models. The important differences are that the "join" model is named differently and it's behavior is more predictable.

In your example, you need a way to add and remove without editing other users Video links, therefore standard habtm will not suit you very well. Create a model for this "join" similar to the Membership model described above. Further, if you added a weight field, you could use the ordered behavior (that I wrote) to order each set of videos per bin. Then you would use the following fields

id, bin_id, video_id, weight

And set up bin_id as the 'foreign_key' in the behavior configuartion. Good luck

Answer 2

Don't know about "most efficient" way to implement what you want, that depends on your whole application and what you want to accomplish. I'd just keep in mind that the most simple solution is often the best.

However, I can give you a tip for sorting your Video array! It is fairly easy:

$yourData = Set::sort($yourData, '/Video/id', 'asc');

Take a look at the Set class for more candy. :)

Answer 3

well I tried to solve just this problem and think I found the simplest solution possible:

When saving a new order of the related models you delete the existing relations and add the new ones in the desired order. This means the ids created run in your sort order and you can simply sort by id to display the order.

public $hasAndBelongsToMany = array(
 'Item' => array(
  'order' => 'ItemFoldersItem.id ASC, Item.name DESC',
 )
);

Pretty simple isn't it?