Use more complex where clauses in Scout

Question

I am a happy user of Laravel Scout.

Now I'd like to extend my search:

        $data
            = new UserOverviewResourceCollection(User::search($searchphrase)
            ->currentStatus('active')->orderBy('lastname', 'asc')
            ->orderBy('firstname', 'asc')
            ->paginate(config('pagination.length')));

currentStatus comes from https://github.com/spatie/laravel-model-status .

Now I am getting a response, that currentStatus is not supported. I thought I could be a good idea to filter the result of User::search after it has been returned from scout?

Another idea: I'd like to add more complex where clauses:

->where([
                [
                    'starts_on',
                    '<',
                    Carbon::now()->toDateTimeString(),
                ],
                [
                    'ends_on',
                    '>',
                    Carbon::now()->toDateTimeString(),
                ],
            ])

Maybe you got a better idea?

Answer 1

I really need similar functionality as well but I'm afraid it's not possible at the moment (Laravel 8)

According to the docs; Laravel\Scout\Builder (which is what search() returns) only has a really basic implementation of the where method an can only handle exactly 2 arguments. Here is the code from the scout package:

/**
 * Add a constraint to the search query.
 *
 * @param  string  $field
 * @param  mixed  $value
 * @return $this
 */
public function where($field, $value)
{
    $this->wheres[$field] = $value;

    return $this;
}

See https://laravel.com/docs/8.x/scout#where-clauses

Putting the search() behind all regular eloquent builder methods won't work either since search will only be a static function of the model.

Hopefully this will be improved in the future.

Answer 2

You can extend Builder and add currentStatus method to store required status in builder. See example https://github.com/matchish/laravel-scout-elasticsearch/issues/47#issuecomment-526287359

Then you need implement your own engine and build query using builder there. Here is example for ElasticSearch engine https://github.com/matchish/laravel-scout-elasticsearch/blob/06c90914668942b23ffaff7d58af5b9db1901fb1/src/Engines/ElasticSearchEngine.php#L135

Answer 3

I first stumbled upon your question when I was having the same problem a couple of days ago, which is when i decided to build my own solution. I created a laravel scout fork, and added the following features :

• An array of arrays can be passed as a parameter : where([['id', '>', 5],['id', '<', 10]]).
• Any operator supported by the Eloquent Builder is now supported as well : '=', '<', '>', '<=', '>=', '!=', etc.
• Clauses containing the operator like so : where('id', '>', 5).
• All changes are fully backward compatible, If no operator is passed as a parameter, it will be assumed that the desired operator is '='

Inside your laravel project, scout is found in : vendor/laravel/ Replace the scout directory by the cloned repository, and you will be able to use the complex where clause normally.

You can find the repository here : https://github.com/alibaddd/scout

Answer 4

Well rewriting it as this should work.

 $data
            = new UserOverviewResourceCollection(User::search($searchphrase)
            ->where('starts_on','<',now())
            ->orderBy('lastname', 'asc')
            ->orderBy('firstname', 'asc')
            ->paginate(config('pagination.length')));

Notice that now() is just a global helper function returning current moment Carbon instance. It's just shorter to write, no other reason for using it.

And if you want grouped where queries you do it like this:

 $data
            = new UserOverviewResourceCollection(User::search($searchphrase)
            ->where(function($q) {
               $q->where('starts_on','<',now())->where('ends_on','>',now())
            })
            ->orderBy('lastname', 'asc')
            ->orderBy('firstname', 'asc')
            ->paginate(config('pagination.length')));

And then you should be able to export this to the UserOverviewResourceCollection as a local scope, for example:

public function scopeActive($q) {
    return $q->where('starts_on','<',now())->where('ends_on','>',now())
}

and then you can use something like this:

 $data
            = new UserOverviewResourceCollection(User::search($searchphrase)
            ->active()
            ->orderBy('lastname', 'asc')
            ->orderBy('firstname', 'asc')

I've written this from my head so there might be typos.

Answer 5

 $data
        = new UserOverviewResourceCollection(User::search($searchphrase)
        ->currentStatus('active')->orderBy('lastname', 'asc')
        ->where(function($q){
            $query->where('starts_on', '<',Carbon::now()->toDateTimeString());
            $query->where('ends_on', '>',Carbon::now()->toDateTimeString());
         })
        ->orderBy('firstname', 'asc')
        ->paginate(config('pagination.length')));

Try this code. This will satisfy your complex where condition