The Price model casting creates a big bottleneck.

[alecritson]

When interacting with prices, the totals are cast to a Price datatype, this fetches info such as currency and customer groups. The issue is when you are dealing with a large number of records, any interaction with Prices will result in quite a big bottleneck.

This wouldn't be so bad if the casting was an "opt-in" thing, but since it happens automatically it can't really be avoided and if you were to try and index a large number of products, with a few variants each with pricing or tiered pricing, it will grind to a halt and in most cases run out of memory.

[glennjacobs]

The Price datatype only uses currency. Would eager loading of the currency prevent this?

Do you have example code to reproduce the problem?

[alecritson]

I did try eager loading it but it was still timing out, maybe it just needs some tweaks.

Reproducing just involved seeding around 3k products, each with between 1-15 variants, each had just one base price.

Then in a product observer, doing something like:

$minPrice = $product->variants->pluck('basePrices')->flatten()->sortBy('price.value')->first();
$product->addSearchableAttribute('min_price', $minPrice->price->value);

It got to around 1.5k products and then bombed out and took ages per 500 records

I tried this instead just to experiment.

$minPrice =  \DB::table('prices')
            ->whereIn('priceable_id', $product->variants->pluck('id'))
            ->orderBy('price')
            ->first();
$product->addSearchableAttribute('min_price', $minPrice->price);

Everything then indexed pretty quickly, surprisingly quickly.

FYI

Since I symlinked the package, I shoved this in the product model for testing:

     /**
     * Modify the query used to retrieve models when making all of the models searchable.
     *
     * @param  \Illuminate\Database\Eloquent\Builder  $query
     * @return \Illuminate\Database\Eloquent\Builder
     */
    protected function makeAllSearchableUsing($query)
    {
        return $query->with([
            'defaultUrl',
            'thumbnail',
            'variants.thumbnail',
            'variants.basePrices.currency',
            'collections.parent.parent',
            'variants.values.option',
        ]);
    }

[ryanmitchell]

Sorry to chip in, but related I'm seeing a lot of duplicate queries to Currency::getDefault(). I wonder if its worth wrapping the response from getDefault in the HasDefaultRecord trait in a Blink::once(), so the query is only run once per request?

[alecritson]

I've not seen Blink before, is this a Laravel feature? I couldn't see anything in the docs.

[ryanmitchell]

Sorry should have linked to it:
https://github.com/spatie/blink

[netzknecht]

Another approach I dealt with is to use Laravel Eloquent Query Cache to prevent firing some queries over and over again. The packages allows "for ever" caching ($cacheFor = -1) of queries and comes with "automatic cache invalidation" - but be aware, many-to-many relationships needs some extra attention for automatic invalidation.

[zalito12]

I'm also noticing this. At first I thought it was something with my views but after some testing with api I see the same problems.

Anything related with prices will blowup queries, for example, I'm making some tests with Products and Product Variants. Take this ProductResource:

class ProductResource extends JsonResource
{
    public function toArray(Request $request): array
    {
        return [
            'id' => $this->id,
            'variants' => ProductVariantResource::collection($this->whenLoaded('variants')),
        ];
    }
}

And this Variant product resource:

class ProductVariantResource extends JsonResource
{
    public function toArray(Request $request): array
    {
        return [
            'id' => $this->id,
            'sku' => $this->sku,
//            'price' => $this->pricing()->get()->matched->price->formatted(),
            'images' => $this->images->map->only(['id', 'name', 'original_url']),
            'options' => ProductOptionValueResource::collection($this->values),
        ];
    }
}

A paginated request like this takes 10 Queries:

        return ProductResource::collection(Product::query()
            ->with([
                'thumbnail',
                'urls.language',
                'variants.basePrices',
                'variants.values.option',
                'variants.images'
            ])
            ->withCount('variants')
            ->paginate());

But... As soon as I add the prices, it goes up to 180 Queries 💣

class ProductVariantResource extends JsonResource
{
    public function toArray(Request $request): array
    {
        return [
            'id' => $this->id,
            'sku' => $this->sku,
            'price' => $this->pricing()->get()->matched->price->formatted(),
            'images' => $this->images->map->only(['id', 'name', 'original_url']),
            'options' => ProductOptionValueResource::collection($this->values),
        ];
    }
}

From the 180 queries, 165 are duplicated, repeating this for each variant:

select
  *
from
  `lunar_product_variants`
where
  `lunar_product_variants`.`id` = 56
  and `lunar_product_variants`.`deleted_at` is null
limit
  1;

select * from `lunar_currencies` where `lunar_currencies`.`id` = 1 limit 1;

select
  *
from
  `lunar_prices`
where
  `lunar_prices`.`priceable_type` = 'product_variant'
  and `lunar_prices`.`priceable_id` = 56
  and `lunar_prices`.`priceable_id` is not null;

I tried adding 'variants.basePrices.currency' but it just does nothing, it goes to 181 queries instead 😆

May be I'm missing a key realtionship, idk.

[zalito12]

With the last laravel version 12.8, the use of Model::automaticallyEagerLoadRelationships() reduces this problem