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'.)

I don't know how you would do it in laravel, but I know that in a straight mysql query if you want something ordered by a pre-defined order you can use the field or find_in_set as part of your order by and just listing out values in whatever order you want. Or you could use a case statement in the select (or order) that returns numbers and order by that column. Unless laravel has another way, the only other way I can think of would be to store the items in an array and usort using your array of ids to get the desired order. — Jonathan Kuhn
– Jonathan Kuhn, Commented Jan 23, 2015 at 20:48
@Matthew Brown $list->item_order is ordered array of items ids?? — voodoo417
– voodoo417, Commented Jan 23, 2015 at 20:53

Matthew Brown · Accepted Answer · 2015-01-28 20:42:05Z

46

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);
}

edited Jan 28, 2015 at 20:42

Matthew Brown

5,1564 gold badges23 silver badges22 bronze badges

answered Jan 23, 2015 at 22:53

lukasgeiter

154k29 gold badges349 silver badges280 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matthew Brown Over a year ago

I did end up using this method, works great. I had no idea the sortBy function also accepted closures.

patricus · Accepted Answer · 2015-01-24 02:35:08Z

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.

miken32 · Accepted Answer · 2022-01-19 20:36:04Z

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;

Collectives™ on Stack Overflow

Sorting Laravel Collection via Array of ID's

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related