When using eloquent to select a model with many to many relationship, it's very simple like:
$orders = Order::with('products')->whereHas('products', function($query){
$query->where('id', '=', 1);
});
This query will return all orders with the products for each order where order has a product with id = 1
. When an order has many products, the query will return all products if order has a product with id = 1
,
but when using query builder to return the same result as eloquent, it's returning for each order just one product with id = 1
.
Here is the query:
DB::table('orders')->join( 'order_product', 'orders.id', '=', 'order_product.order_id' )
->join( 'products', 'products.id', '=', 'order_product.product_id' )
->whereIn('products.id', $this->products);
How to get all products for each order when order has a product with id = 1
even when using where
clause?