Nivesh Saharan Nivesh Saharan - 3 months ago 52
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', 'orders.id', '=', 'order_product.order_id');
$orders->join('products', 'products.id', '=', 'order_product.product_id');
$orders->join('customers', 'order_product.order_id', '=', 'customers.order_id');
$orders->join('addresses', 'customers.id', '=', '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;
});
}

$orders->groupby('orders.id');

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

$orders->orderBy($orderBy, $orderDirection)->skip($startFrom)->take($itemsPerPage);
$orders = $orders->select([
'orders.id',
'orders.order_name',
'products.title',
'products.handle',
'products.id as product_id',
'orders.status'
])->get();

Answer

Copy from my comments:

I assume you want to paginate $orders.

$orders->paginate($itemsPerPage)

Gives you exact same results as

$orders->skip($startFrom)->take($itemsPerPage)->get().

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.

Comments