How to Group Database Records by Days Using Laravel Eloquent?

Querying Database Records Grouped by Days Using Laravel Eloquent

When working with databases, it's often necessary to aggregate data by certain intervals, such as days. In the case of Laravel Eloquent, the groupBy() method allows us to perform such grouping, but time-based columns like created_at may pose a challenge when using this method.

Problem: Grouping by days considering seconds

The issue arises because created_at columns typically include hours, minutes, and seconds, resulting in different timestamps for events happening on the same day. As a result, when grouping by created_at, records for the same day may be separated due to the time difference.

For instance, consider the following sample data:

created_at	page_id	user_id
2023-10-11 01:02:03	3	1
2023-10-12 12:34:56	5	5
2023-10-13 15:47:12	5	2
2023-10-13 18:23:09	3	4

If we try to group this data by created_at using the Eloquent command:

<code class="php">$visitorTraffic = PageView::groupBy('created_at')->get();</code>

We'll end up with separate rows for each time a visitor accessed a page on a particular day, which is not the desired result.

Solution: Using Raw Query and Carbon Dates

To overcome this issue, one solution is to use raw SQL queries. In MySQL, the DATE() function can be used to convert a timestamp to its corresponding date, allowing us to group by days without considering the time component. The following query can be used:

<code class="sql">DB::table('page_views')
->selectRaw('DATE(created_at) AS date, COUNT(*) AS views')
->groupBy('date')
->get();</code>

Alternatively, a more Laravel Eloquent-centric approach can be employed using Carbon dates:

<code class="php">$visitorTraffic = PageView::where('created_at', '>=', Carbon::now()->subMonth())
->groupBy(DB::raw('DATE(created_at)'))
->orderBy('date', 'DESC')
->get([
    DB::raw('DATE(created_at) AS date'),
    DB::raw('COUNT(*) AS views')
]);</code>

By utilizing the DATE() function or Carbon dates, we can group database records by days while ensuring that records representing the same day are aggregated together.

The above is the detailed content of How to Group Database Records by Days Using Laravel Eloquent?. For more information, please follow other related articles on the PHP Chinese website!