Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I am struggling to merge these two queries into a single query. I have tried joins and subquery but could solve this. I am using Laravel, I am executing one query in the Controller and the other in the blade file. Controller code:
$orders = Order::select([
DB::raw('id'),
DB::raw('wp_order_id'),
DB::raw('customer_name'),
DB::raw('pickup_location'),
DB::raw('vendor_id'),
DB::raw('date_created'),
])
->where('orders.store_id', $vendor->store_id)
->where('orders.vendor_id', $vendor->wp_vendor_id)
->whereIn('status', ['processing', 'pending'])
->groupBy(['wp_order_id'])
->get();
Blade code:
@forelse ($orders as $item)
<tr>
<td>
@php
$products = \App\OrderProduct::select([
DB::raw('product_name'),
DB::raw('count(product_id) as number_of_items'),
])
->where('wp_order_id', $item->wp_order_id)
->groupBy('product_id')
->get();
@endphp
@forelse ($products as $product)
{{ $product->number_of_items . ' x ' . $product->product_name }} <br>
@empty
@endforelse
</td>
</tr>
@empty
@endforelse
Kindly help me solve this.
@mansoorkhan8 its definitely not recommended doing a query in a foreach loop to get a list of products.
Correct way to do this is to define some model relationships
so, I would hazard you have Order hasMany Product ?
can you show the migrations for what you've currently got? I assume a orders, products, order_product table.
Thanks for the response @automica I have refactored the code and now it looks like this:
Order::with(['product' => function($query) {
$query->groupBy('product_id');
}])
->where('orders.store_id', $vendor->store_id)
->where('orders.vendor_id', $vendor->wp_vendor_id)
->whereIn('orders.status', ['processing', 'pending'])
->groupBy(['orders.wp_order_id'])
->take(10)->get()->toArray()
But when I try to put select in inner/subquery it does not return relation data for product. Here is what I am trying:
Order::with(['product' => function($query) {
return $query->select([
DB::raw('product_name'),
DB::raw('count(product_id) as number_of_items')
])->groupBy('product_id');
}])
->where('orders.store_id', $vendor->store_id)
->where('orders.vendor_id', $vendor->wp_vendor_id)
->whereIn('orders.status', ['processing', 'pending'])
->groupBy(['orders.wp_order_id'])
->take(10)->get()->toArray()
I am using select in subquery because I want the count for each group in the subquery. I hope it makes sense
@mansoorkhan8 I think use withCount to get a count of your related products
https://laravel.com/docs/8.x/eloquent-relationships#counting-related-models
I have tried withCount() but it does not return proper data. It returns the just count of all products for one order. I want separate count (also the other product details) for each groupBy('product_id') group.
@mansoorkhan8 if you could post your migrations, then I will be able to try it locally and see what I can discover.
I have figured it out. Thank you so much for your efforts, peace be upon you :) This is the code now:
$orders = Order::with(['product'])
->where('orders.store_id', $vendor->store_id)
->where('orders.vendor_id', $vendor->wp_vendor_id)
->whereIn('orders.status', ['processing', 'pending'])
->groupBy(['orders.wp_order_id'])
->get();
foreach($orders as $order) {
$products = $order->product->groupBy('product_id');
foreach($products as $product) {
}
}
Thank you for your time.
@mansoorkhan8 cool. i'd recommend you look at adding debugbar, to ensure you aren't going to suffer the n+1 problem.
@automica thanks for the recommendation. I have it. Pages are much faster now.
Please or to participate in this conversation.