How do I get a "select count(*) group by" using laravel eloquent
Stefan Izdrail
Founder & Senior Architect · 2026-06-29
Title: Achieving "select count(*) group by" Using Laravel Eloquent for Advanced Querying
There might be several ways to tackle this problem using Laravel Eloquent. First, let us understand the concept behind the SQL statement you wish to achieve. This query would retrieve rows with all columns (*) and count the total number of records per day grouped by them. Now, we'll explore ways to implement this in Laravel:
1. Using Raw SQL Query: You can use a raw SQL query to execute the desired statement directly from your Laravel application. Here's an example:
```php
// Retrieve data using a raw SQL query
$data = DB::select('SELECT *, count(*) AS total_count FROM reservations GROUP BY day');
foreach ($data as $row) {
echo 'Day: ' . $row->day . ', Total Count: ' . $row->total_count;
}
```
2. Using Laravel's DB Facade for Grouping and Counting: This approach leverages the power of native Laravel Eloquent functions to accomplish the same goal. Let's break down this example step by step:
a. Collect all reservations, grouped by day:
```php
$groupedReservations = Reservation::select('day')->groupBy('day');
```
b. Iterate through the collections and perform count() to get total counts:
```php
foreach ($groupedReservations as $reservation) {
echo 'Day: ' . $reservation->day . ', Total Count: ' . Reservation::whereDay($reservation->day)->count();
}
```
3. Using Laravel Eloquent with a Query Builder: Using the Laravel query builder can be another elegant solution for this problem. Here's how you would do it:
```php
// Retrieve reservations grouped by day using a Laravel query builder
$groupedReservations = Reservation::select('day')
->groupBy('day')
->get();
// Iterate through collections and count the records using the model's static count() method
foreach ($groupedReservations as $reservation) {
echo 'Day: ' . $reservation->day . ', Total Count: ' . Reservation::whereDay($reservation->day)->count();
}
```
4. Using Laravel Eloquent with Dynamic SQL and Groups: This method will generate the required dynamic SQL statement based on your needs, but it requires you to be cautious about potential SQL injection vulnerabilities. Here's how you can use this approach:
```php
// Create a dynamic SQL statement for grouping and counting based on selected columns
$columns = ['day'];
$sqlStatement = 'SELECT ' . implode(', ', $columns) . ', count(*) AS total_count FROM reservations GROUP BY ' . implode('_, ', $columns);
// Execute the dynamic SQL statement using Laravel's query builder
$groupedReservations = Reservation::query()
->selectRaw($sqlStatement)
->get();
foreach ($groupedReservations as $reservation) {
echo 'Day: ' . $reservation->day . ', Total Count: ' . $reservation->total_count;
}
```
In conclusion, the methods provided above can help you achieve your desired outcome of fetching data from your database with a count for each day. Each approach has its own advantages and disadvantages. It is essential to choose the one that best suits your application's needs and your team's skillset. Finally, remember to implement appropriate security measures when dealing with dynamic SQL statements to ensure your application remains secure.