Support the ongoing development of Laravel.io →
Eloquent
Last updated 2 years ago.
0
moderator

I think the first problem is the use of the withCount for the SUM statements, I suspect it runs the sum query and your raw query (and because you use the same name you will get the last result what is your raw query)

Partial example that maybe can help you.

$products = Product::whereHas('store', function ($query) {
    return $query->where('user_id', Auth::id());
})->with('postings')->when($filters, function ($q) use ($filters) {
    if (isset($filters['stores'])) {
        $q->whereIn('store_id', $filters['stores']);
    }
})
    ->addSelect([
        'units_ordered' => Posting::selectRaw('SUM(quantity) as units_ordered')
            ->when(isset($filters['date']), function ($quantitySumFilterQuery) use ($filters) {
                $quantitySumFilterQuery->whereDate('in_process_at', '>=', $filters['date']['from'])
                    ->whereDate('in_process_at', '<=', $filters['date']['to']);
            })
            ->whereRaw('id =products.id')
    ]);
]);

Some smaller questions are:

  1. Do you have indexes on your tables? They can make a big difference.
  2. You can test the use for between with the dates
  3. You can run the query with explain to see how it is handled to get more information.

Sidenote, I suggest that you use more clearly variable names. I see different $q without knowing which query it is.

0

Sign in to participate in this thread!

Eventy

Your banner here too?

Manuk manukminasyan Joined 17 Aug 2021

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.