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:
Sidenote, I suggest that you use more clearly variable names. I see different $q without knowing which query it is.
Sign in to participate in this thread!
The Laravel portal for problem solving, knowledge sharing and community building.
The community