orderBy doesn't work

[calvinmuller]

Hi there,

I'm using scout with algolia to index a large database of products, however when trying to sort the results, i'm not getting the desired results.

$products = Product::search('')
                ->orderBy('name', 'asc')
                ->paginate();

Anything i'm missing for this to return results ordered by name?

[julienbourdeau]

Hi @calvinmuller,

Algolia doesn't not allow ordering at query time. The data are ordered while being indexed, which makes the query much faster. You will need to create a replicas in your Algolia dashboard and sort this index by name.
Please have a look at: https://www.algolia.com/doc/tutorials/indexing/ranking/how-to-sort-the-results-with-a-specific-attribute/index.html

Then with Scout, you can use the within method to query the desired index.

$products = Product::search('')
                ->within('products_by_name')
                ->paginate();

[calvinmuller]

nailed it, thanks!

[peterpan666]

Hi, sorry to post on this closed issue but I've tried @julienbourdeau solution and the results are always ordered by IDs...

Anything on this?

[nunomaduro]

@peterpan666 Thanks for bring that to our attention. I just have added your issue into my todo list, and I will check it soon as possible.

[peterpan666]

Wow thanks @nunomaduro I've hesitated to send you an email but thanks for noticing this issue 😊

Maybe should I open another issue?

[nunomaduro]

@peterpan666 Just made a quick test with @julienbourdeau solution and everything works as expected. Could you share the code you are using?

[peterpan666]

Hi @nunomaduro, happy new year and thanks for the reply!

I've managed to do some tests, here are the results.

I've got a model called Document which use the Searchable trait from Scout. The only method I've written is the following.

public function toSearchableArray()
{
    return [
        'id' => $this->id,
        'title' => $this->title,
        'created_at' => $this->created_at->timestamp,
    ];
}

On Algolia, I've one documents index (which is the default for my model and I kept the default config from Algolia) and a documents_latest index which is a replica of documents and with the following config.

Now I want to load them like so

dd(
    Document::search('')->paginate(20)->pluck('id'),
    array_pluck(Document::search('')->raw()['hits'], 'id'),
);

So the first line will fetch records from database and pluck the ids, the second line will fetch records from Algolia and display the ids.

We can see that the ids are the same but not ordered the same way.

And it's also true if I load the records from a different index

dd(
    Document::search('')->within('documents_latest')->paginate(20)->pluck('id'),
    array_pluck(Document::search('')->within('documents_latest')->raw()['hits'], 'id')
);

Am i missing something?

[nunomaduro]

No worries, I am here to help you.

First, please check if $this->created_at->timestamp on the toSearchableArray is integer.

[peterpan666]

Thanks @nunomaduro !

Yes it is, the raw result from Algolia are ok and in the right order, I think the problem comes up when Laravel load the records from my database, the ids are then just ordered ASC...

Here is a screenshot from one Algolia record

[peterpan666]

Here is the request executed by Laravel to fetch data to my database, I think I'm missing something because there is nothing in the request to order the results so I don't know how Scout should do it...

[nunomaduro]

I see. I will run a quick test tomorrow, and open a pull request to fix this issue.

[peterpan666]

Ok great! I've got a workaround for this but I wasn't sure that was the greatest way to do this or if I was missing something...

What I've done now is rewriting the getScoutModelsByIds method on the Searchable trait to order it correctly like so

public function getScoutModelsByIds($builder, array $ids)
{
    $query = static::usesSoftDelete()
        ? $this->withTrashed() : $this->newQuery();

    if ($builder->queryCallback) {
        call_user_func($builder->queryCallback, $query);
    }

    return $query->whereIn(
        $this->getScoutKeyName(), $ids
    )->orderByRaw('FIELD ('.$this->qualifyColumn($this->getKeyName()).', '.implode(', ', $ids).')')->get();
}

The downside with this is the orderByRaw('FIELD ...'), I don't think this is a working solution for every Eloquent database driver (sqlite, postgre or sql server).

[nunomaduro]

That's not good. The solution is just use the same algorith that I have done for Scout Extended: After resolving the models from the database, just order them using the $ids of Algolia.

[nunomaduro]

@peterpan666 Just created an issue for this: #341.

[peterpan666]

Awesome! Thanks a lot @nunomaduro !

[jandrescossio]

If you are using Meilisearch you can do this:

$products = Product::search($request->input('search'), function ($meilisearch, $query, $options) {
            $meilisearch->updateRankingRules(['desc(name)']);
            return $meilisearch->search($query, $options);
        })->paginate(10);

[mgralikowski]

The Meilisearch offers sorting and it works nice. Ordering requires setting "orderable" attributes before querying. I sent PR with direct implementation of orderBy :
#537

[Crysalist]

Hi there,

I'm using scout with algolia to index a large database of products, however when trying to sort the results, i'm not getting the desired results.

$products = Product::search('')
                ->orderBy('name', 'asc')
                ->paginate();

Anything i'm missing for this to return results ordered by name?

Got it to work by wrapping it into a query:

$products = Product::search('')->query(function($q) {
                $q->orderBy('name', 'asc')
                })->paginate();