scout:import performance issues when toSearchableArray contains relationships

[bkilshaw]

I recently made a model Searchable using Scout and Algolia. When running scout:import in production the whole production (Google App Engine) crashed. There was just over 15K records that needed to import. The SQL queries were going out of control (2000-2500 queries/second) and our production environment was down until the queued jobs all failed.

Someone in Discord (@fitztrev) mentioned that they ran into the same issue and tracked it down to the lack of eager loading of relationships mentioned in toSearchableArray(). Here is the method in my case:

public function toSearchableArray()
    {
        return [
            'provider_user_id' => $this->provider_user_id,
            'provider_name' => $this->provider->display_name,
            'receiver_user_id' => $this->receiver_user_id,
            'receiver_name' => $this->receiver->display_name,
            'receiver_hierarchy' => $this->receiver->role->hierarchy,
            'store_id' => $this->receiver->store_id,
            'category_id' => $this->category_id,
            'category_name' => $this->category->name,
            'subcategory_id' => $this->subcategory_id,
            'subcategory_name' => $this->subcategory->name,
            'comments' => $this->comments,
            'date_occurred'=> $this->date_occurred
        ];
    }

If the relationships aren't eager loaded it would make sense that this would generate an obscene number of queries.

I believe I could use protected $with = ['provider', 'receiver', 'category', 'subcategory']; on the object, but that would mean I'm eager loading the relationships every single time I have an instance of this object, which I don't always want/need to do.

[fitztrev]

Cross ref algolia/scout-extended#90

[driesvints]

Did some research for this. The problem is indeed that the makeAllSearchable builds up a new query. Using protected $with would indeed solve this problem but can't be done for the reason stated above. We could solve this with an extra method on the Searchable trait. Something like this (or a better name):

public function searchableWith(): array
{
    return ['provider', 'receiver', 'category', 'subcategory'];
}

Which then would be used in the makeAllSearchable method. I think this would be the only solution for this and still an appropriate one because like you stated yourself you'd only use this when doing an import. For all other purposes you'd do the eager loading beforehand.

[driesvints]

Gonna mark this as a feature request rather than a bug because the temp solution would indeed be to use the with property.

[tarekbazine]

for now you could just override makeAllSearchable() in your model (the model that is been indexed by scout) so you get something like that :

/**
 * Make all instances of the model searchable.
 *
 * @return void
 */
public static function makeAllSearchable()
{
    $self = new static();

    $softDeletes = in_array(SoftDeletes::class, class_uses_recursive(get_called_class())) &&
        config('scout.soft_delete', false);

    $self->newQuery()
        ->with([
            /*******************************
               your relations goes here
           *******************************/
        ])
        ->when($softDeletes, function ($query) {
            $query->withTrashed();
        })
        ->orderBy($self->getKeyName())
        ->searchable();
}

scout version "laravel/scout": "5.0.*"

[altynbek07]

for now you could just override makeAllSearchable() in your model (the model that is been indexed by scout) so you get something like that :

/**
 * Make all instances of the model searchable.
 *
 * @return void
 */
public static function makeAllSearchable()
{
    $self = new static();

    $softDeletes = in_array(SoftDeletes::class, class_uses_recursive(get_called_class())) &&
        config('scout.soft_delete', false);

    $self->newQuery()
        ->with([
            /*******************************
               your relations goes here
           *******************************/
        ])
        ->when($softDeletes, function ($query) {
            $query->withTrashed();
        })
        ->orderBy($self->getKeyName())
        ->searchable();
}

scout version "laravel/scout": "5.0.*"

But it is not work when queue is true.

[driesvints]

Closing this since the PR was rejected. We probably won't be pursuing this any further.