Framework: [Proposal] Eloquent - get collection by a list of IDs

[deiucanta]

In a current project I needed to get a collection of models using their IDs. This can be easily achieved by Model::whereIn('id', $ids) but I needed them in the exact same order as the $ids array.

Below is a proposed API

$collection = Model::list([1, 18, 14]);

Or it may be implemented directly in the find() method.

$model = Model::find(1); // returns one model
$collection = Model::find([1, 18, 14]); // returns a collection of models

[JoostK]

You can in no way let SQL handle the ordering you specify. You either have to execute N queries, or sort using a custom sort callback when done (better)

Model::find([1, 18, 14]) already returns a collection, but probably ordered 1, 14, 18. The collection returned has some helpful sorting methods available though, perhaps you could take a look at them.

[deiucanta]

Actually there are ways to achieve this in MySQL but are not easy. I was thinking for a sorting (reordering) in PHP. I will post a code example later.

Am I the only one that encountered this need?

[deiucanta]

Here is one way to implement this, but it would be nice to have this integrated in Eloquent / Query Builder by default.

use Illuminate\Database\Eloquent\Collection;

$ids = [3, 2, 1];

$models = Model::find($ids);
$sorted = array_flip($ids);

foreach ($models as $model) $sorted[$model->id] = $model;

$sorted = Collection::make(array_values($sorted));

[bonzai]

What about:

$ids = [1, 18, 14];

// SELECT * FROM `model` WHERE `id` IN (1, 18, 14) ORDER BY FIELD(`id`, 1, 18, 14)
$sorted = Model::whereIn('id', $ids)
    ->orderBy(DB::raw('FIELD(`id`, '.implode(',', $ids).')'))
    ->get();

Note: this example will only work in MySQL.

[tcopestake]

How do you imagine Eloquent/QB determines whether a returned collection should be ordered using the existing behaviour or your proposed behaviour?

[Kindari]

@tcopestake the collection is by default ordered however the database returns the results - so it will respect any order by's done, etc.

[tcopestake]

What I was getting at is, if Model::find([1, 18, 14]) already returns a collection the only remaining option would be to implement this behaviour as a separate method, such as the "list" originally proposed. Otherwise there's a risk of it being considered a BC break (albeit very slight), concerns about the performance penalty, etc.

[deiucanta]

You're right @tcopestake. Model::find([1, 18, 14]) would not be appropriate for this behaviour. Model::list([1, 18, 14]) should be fine.

[JoostK]

I think you're best of implementing this as a method on your Eloquent subclass yourself. I've never come across needing something like this and I'd argue that it's probably a rare use-case, specialized enough to keep out of the core.

Your solution with the IDs flipped as keys is very smart btw :)

[Anahkiasen]

What is the use case by the way ?

[deiucanta]

Sometimes you have to cache content. If you cache a collection with all attributes you may have inconsistencies between cache and database. You could cache an array of IDs and then get the fresh models from the database.

Real world example: Top daily users by certain algorithm. You need to cache those users in order, once a day. If they change their profile image, name or their score changes, your cache will be inconsistent. Storing only the IDs in order will get you better data.

[taylorotwell]

You will need a helper function to do this for you. It just won't fit very cleanly into Eloquent. I've had to write a helper function in my own project to do it.

[jgb-solutions]

I just found this thread on Google by searching for the same thing. I'm building a music playlist feature in an app. I get the music ids using the playlist of musics from a MusicList model. Then I fetch the musics using these ids. And I need them to be ordered the same they were in the music lists table. So now I'm going to try the methods listed here. This is a handy thing to do I think. Would be nice to have in Laravel since Laravel seems to do almost all the hard work for us developers. ;)

[IvanBernatovic]

@deiucanta @jgbneatdesign I know it's too late, but it may be helpful for others in the future. If your original list of IDs is coming from database directly (and not from end user) than it's almost certain that you can use JOIN statements to sort original model. For example, I have models Post (table posts) and Tag (table tags). They are connected via polymorphic many to many relationship by using taggables table. If I want to fetch list of most frequently used tags I have similar use case like yours: first I get list of tag IDs by counting all of the records and grouping by tag_id in taggables table, then order it by frequency (DESC) and I take only 20 rows. Now I have your use case, I have list of tag ids ordered by frequency and I need to fetch that Tags ordered just like those IDs. Instead of writing helper methods and doing it in code I could use Eloquent and left join statements like this:

$tags = Tag::leftJoin('taggables', 'tags.id', '=', 'taggables.tag_id')
                ->groupBy('taggables.tag_id')
                ->orderBy('frequency', 'DESC')
                ->take(20)
                ->get([\DB::raw('tags.*, count(*) AS frequency, taggables.tag_id')]);

So I did it all in one query and I eliminated need to do it in code. This is only helpful if you get your list of IDs from database first which in @jgbneatdesign case you do.

[markushartman]

For my own project i added it to the Collection as a macro based on the idea of @deiucanta deiucanta , maybe it will help someone....

            /**
             * Sort models to their original list.
             *
             * example:
             *       $ids = [50, 3] or collection
             *       $professionals = User::findMany($ids)
             *                                      ->sortToOriginal($ids);
             *
             * @param  \Illuminate\Contracts\Support\Arrayable|array  $ids
             * @return \Illuminate\Database\Eloquent\Collection
             */
            Collection::macro('sortToOriginal', function ($ids): Collection
            {
                $ids = $ids instanceof Arrayable ? $ids->toArray() : $ids;

                $models = array_flip($ids);

                foreach ($this as $model) {
                    $models[ $model->id ] = $model;
                }

                return Collection::make(array_values($models));
            });