Sorting Laravel Collection via Array of ID's

Question

Is it possible to order a relationship collection using a separate array of ID's while still accessing through the relationship?

The setup is Checklist has many ChecklistItems and the desired order of the related items exists as a property Checklist::$item_order. It is just an array of numeric ID's in the user's desired order. :

class Checklist extends Model {
    protected $casts = ['item_order' => 'array'];

    public function items() {
        return $this->hasMany(ChecklistItem::class);
    }
}
class ChecklistItem extends Model {
    public function list() {
        return $this->belongsTo(Checklist::class);
    }
}

I access this relationship the normal way:

$list = Checklist::find(1234);

foreach ($list->items as $item) {
    // More Code Here
}

Is there a feasible way to order $list->items based on the values in the $list->item_order array?

(I can't just add an 'order' column to the 'item' table, b/c the order changes for each 'list'.)

Answer 1

I recently had to do something like this, but with the added requirement that not all the items had a specific order. The remaining items without a specified order had to be alphabetically ordered.

So I tried the strategy in the accepted answer, ordering the collection with a custom callback that tried to account for the missing items. I got it working with multiple calls to orderBy() but it was pretty slow.

Next I tried the other answer, sorting first with the MySQL FIND_IN_SET() function and then with the item name. I had to sort by FIND_IN_SET(...) DESC to ensure the listed items came at the top of the list, but this also caused the ordered items to be in reverse order.

---

So I ended up doing something like this, doing a sort in the database by an equality check on each item in the array, and then by the item name. Unlike the accepted answer, this is done as a simple relationship method. That means I get to keep Laravel's magic properties, and anytime I get that list of items it's always sorted correctly without special methods having to be called. And since it's done at the database level, it's faster than doing it in PHP.

class Checklist extends Model {
    protected $casts = ['item_order' => 'array'];

    public function items() {
        return $this->hasMany(ChecklistItem::class)
            ->when(count($this->item_order), function ($q) {
                $table = (new ChecklistItem)->getTable();
                $column = (new ChecklistItem)->getKeyName();
                foreach ($this->item_order as $id) {
                    $q->orderByRaw("`$table`.`$column`!=?", [$id]);
                }
                $q->orderBy('name');
            });
    }
}

Now doing this:

$list = Checklist::with('items')->find(123);
// assume $list->item_order === [34, 81, 28]

Results in a query like this:

select * from `checklist_items`
where `checklist_items`.`checklist_id` = 123 and `checklist_items`.`checklist_id` is not null
order by `checklist_items`.`id`!=34, `checklist_items`.`id`!=81, `checklist_items`.`id`!=28, `name` asc;

Answer 2

You can do this:

$order = $list->item_order;
$list->items->sortBy(function($model) use ($order){
    return array_search($model->getKey(), $order);
}

Also you could add an attribute accessor to your model which does the same

public function getSortedItemsAttribute() 
{
    if ( ! is_null($this->item_order)) {
        $order = $this->item_order;

        $list = $this->items->sortBy(function($model) use ($order){
            return array_search($model->getKey(), $order);
        });
        return $list;
    }
    return $this->items;
}

Usage:

foreach ($list->sortedItems as $item) {
    // More Code Here
}

---

If you need this sort of functionality in multiple places I suggest you create your own Collection class:

class MyCollection extends Illuminate\Database\Eloquent\Collection {

    public function sortByIds(array $ids){
        return $this->sortBy(function($model) use ($ids){
            return array_search($model->getKey(), $ids);
        }
    }
}

Then, to actually use that class override newCollection() in your model. In this case it would be in the ChecklistItems class:

public function newCollection(array $models = array())
{
    return new MyCollection($models);
}

Answer 3

You can try setting up a relationship that returns the results in the order for which you're looking. You should still be able to eager load the relationship, and have the results in the specified order. This is all assuming the item_order field is a comma separated string list of ids.

public function itemsOrdered()
{
    /**
     * Adds conditions to the original 'items' relationship. Due to the
     * join, we must specify to only select the fields for the related
     * table. Then, order by the results of the FIND_IN_SET function.
     */
    return $this->items()
        ->select('checklist_items.*')
        ->join('checklists', 'checklist_items.checklist_id', '=', 'checklists.id')
        ->orderByRaw('FIND_IN_SET(checklist_items.id, checklists.item_order)');
}

Or, if you don't want to hardcode the tables/fields:

public function itemsOrdered()
{
    $orderField = 'item_order';

    $relation = $this->items();
    $parentTable = $relation->getParent()->getTable();
    $related = $relation->getRelated();
    $relatedTable = $related->getTable();

    return $relation
        ->select($relatedTable.'.*')
        ->join($parentTable, $relation->getForeignKey(), '=', $relation->getQualifiedParentKeyName())
        ->orderByRaw('FIND_IN_SET('.$related->getQualifiedKeyName().', '.$parentTable.'.'.$orderField.')');
}

Now, you can:

$list = Checklist::with('items', 'itemsOrdered')->first();
var_export($list->item_order);
var_export($list->items->lists('id'));
var_export($list->itemsOrdered->lists('id'));

Just a warning: this is fairly experimental code. It looks to work with the tiny amount of test data I have available to me, but I have not done anything like this in a production environment. Also, this is only tested on a HasMany relationship. If you try this exact code on a BelongsToMany or anything like that, you'll have issues.