Storing object order efficiently (SQL, PHP)

In my projects our frontend developer makes it possible to re-order objects using drag-and-drop. When the new order of the objects is established, he executes a callback passing me ordered list of object IDs:

[38, 5, 20, 42]

My task is to store this order in the database making sure those objects appear in exactly this order.

There are few catches however such as having initial order and also working with a sub-sets of data.

In other words — our user may “filter” results first before re-ordering them and the new order should not affect other record order. Finally the entire thing must be efficient and quick working with standard database (such as SQL).

In this article I’ll share my technique, so that you can implement it in your own projects. I’m using ATK Data to keep syntax brief and cross-database compatibility.

Setting the default order

You would need to have a column, where we could store some values in ascending order. I usually copy value of id but you can also generate unique numbers in ascending order, e.g.: time().uniqid()

Copying value of “id” into “ord”

After record is inserted (or data is imported), perform update to populate ord column:

$this->addField('ord', ['type'=>'integer', 'system'=>true]);
$this->addHook('afterInsert', function($m, $id) {
$this->action('update')
->set('ord', $this->expr('id'))
->where('ord is null')
->execute();
});
$this->setOrder('ord');

This code copies id values into ord column. The alternative is to create expression:

$this->addExpression('real_ord', 'coalesce([ord], [id])');
$this->setOrder('real_ord');

Allocating “ord” to unique values

Allocating random values may be easier to set-up and is probably more efficient too. This is the only necessary code:

$this->addHook('beforeInsert', function($m) {
$m['ord'] = time().uniqid();
});

Reordering

I have created a simple yet universal method to arrange multiple records in specific order. Here is the code and my explanation will follow:

function reorder($ids) {
$this->persistence->atomic(function() use ($ids) {

// Get ID/ORD pairs
$ord = [];
$query = $this->action('select') // setOrder implied
->reset('field')
->field('ord')
->where('id', $ids); // id in (...)
foreach($query as $row) {
$ord[] = $row['ord'];
}
while($ids) {
$upd = $this->action('update')
->set('ord', array_shift($ord))
->where('id', array_shift($ids))
->update();
;
}

});
}

To explain how this works, lets assume the following database state:

id: Name (ord)
1: John (1)
3: Peter (2)
2: Steve (3)
7: Archer (7)

The call would be reorder(7, 2). The execution happens in two stages. First we query field ord from the database preserving original order but only for the records 7 and 2: Result will be:

$ord = [3, 7]

Next we would need to assign those values to records 7 and 2 respectively, so two queries are executed:

  • ord = 3 where id = 7
  • ord = 7 where id = 2

After the change the database would contain:

id: Name (ord)
1: John (1)
3: Peter (2)
7: Archer (3)
2: Steve (7)

This orders “Archer” before “Steve” but does not affect any other records, which may not have been visible to the user during sort (due to conditions, pagination etc)

Keeping ord values unique

This will work perfectly but you must make sure that the operation is atomic (executed within single transaction). If the record “2, Steve” is deleted, then order “7” value will be lost, but that’s OK because we simply need to keep those values unique.

When adding a new record at the end, the ID of new record will be the highest so will automatically place it at the end of the list.