Recently in one of our client’s projects, we want to load the count of relation in laravel. But we do not want to retrieve original records.
For example,
We have the following Models,
- Category
- Products
- Orders
For that, we have categories, products, orders, order_items table. Where in the order_items table, we got the following fields
- order_id
- product_id
- quantity
So the requirement was, In the Products table, we want to display the total number of orders placed with that item regardless of the quantity in each order. All we need is a number of orders where the product is purchased.
1st way: Query via Relationship
$products = Product::all();
$productsArr = $products->map(function (Product $product)
{
$productObj = $product->toArray();
$productObj['orders_count'] = $product->orders()->count();
return $productObj;
}
);
But the problem with this approach was, we are firing queries to the database for every single product. so if I’m retrieving 100 Products from the database then it will fire 100 additional queries to the database. Imagine if we have thousands of products.
2nd way: Eager Load Relationship and Calculate Count​
$products = Product::with('orders')->get();
$productsArr = $products->map(function (Product $product)
{
$productObj = $product->toArray();
$productObj['orders_count'] = $product->orders->count();
return $productObj;
}
);
so this way, we are only firing two queries to the database. But the problem here is, we are loading all the Orders of each product which we don’t need at all. so it will consume lots of memory since we are loading lots of orders. so imaging if we retrieve 100 products, and each product has 10 orders, then we are loading 1000 Orders into memory without any need.
3rd way: Use withCount function
The third powerful approach of using withCount function in Laravel. so we refactored our code like,
$products = Product::withCount('orders')->get();
$productsArr = $products->map(function (Product $product)
{
$productObj = $product->toArray();
$productObj['orders_count'] = $product ->getAttribute('orders_count');
return $productObj;
}
);
In this approach, we are firing two queries but no Order models are loaded into memory.
4th Bonus: Using in a nested relationship while multiple eager loading
You can even use it with nested relationships. Imagine a case, where you want to retrieve categories along with its products with orders count.
$categories = Category::with(['products' => function ($query)
{
$query->withCount('orders');
},
'someOtherEagerLoading1',
'someOtherEagerLoading2'
])->get();
$categoriesArr = $categories->map(function (Category $category)
{
$categoryObj = $category->toArray();
$categoryObj['products'] = $category->products->map(function (Product $product)
{
$productObj = $product->toArray();
$productObj['orders_count'] = $product ->getAttribute('orders_count');
return $productObj;
});
return $categoryObj;
});
Hope this will help you to retrieve the count of relationship data without retrieving actual relation data.
