Nivesh Saharan Nivesh Saharan - 8 months ago 83
MySQL Question

Laravel 5 - How to count total records with joins and group by

I need to count total no. of records coming from database with group by. I don't need count of each set or what group by does by default.

One way i know is to use ->get() on query, but it crashes when i have a lots of record in database.

$orders = Order::where('orders.store_id', $store->id);
$orders->join('order_product', '', '=', 'order_product.order_id');
$orders->join('products', '', '=', 'order_product.product_id');
$orders->join('customers', 'order_product.order_id', '=', 'customers.order_id');
$orders->join('addresses', '', '=', 'addresses.customer_id');
$orders->where('products.status', 1);
$orders->where('orders.is_deleted', '0');

if ($keyword) {
$orders->where(function ($query) use ($keyword, $searchKeyword){
$query->where('products.title', 'LIKE', $searchKeyword)
->orWhere('orders.order_name', 'LIKE', $searchKeyword);

if (strtolower($keyword) == 'enabled') {
$query->orWhere('orders.status', '=', 1);
}elseif (strtolower($keyword) == 'disabled') {
$query->orWhere('orders.status', '=', 0);

return $query;


// Total orders
$totalOrders = count($orders->get());

$orders->orderBy($orderBy, $orderDirection)->skip($startFrom)->take($itemsPerPage);
$orders = $orders->select([
' as product_id',


Copy from my comments:

I assume you want to paginate $orders.


Gives you exact same results as


There's a slight difference, because Model::get() gives you Collection instance, while Model::paginate() returns LengthAwarePaginator. However you can iterate over both.
Please see Pagination on laravel docs.