Distinct and GroupBy
In the Laravel-Elasticsearch integration, distinct() and groupBy() methods play a pivotal role in data aggregation, particularly for cases where unique values or grouped data summaries are required. These methods leverage Elasticsearch's term aggregation capabilities, offering powerful data analysis tools directly within the Eloquent model.
For this Elasticsearch implementation, the distinct()
and groupBy()
methods are interchangeable and will yield the same results.
Basic Usage
The distinct()
and groupBy()
methods are used to retrieve unique values of a given field.
Distinct
$users = UserLog::where('created_at', '>=', Carbon::now()->subDays(30))
->distinct()->get('user_id');
// Alternative syntax, explicitly selecting the field
$users = UserLog::where('created_at', '>=', Carbon::now()->subDays(30))
->select('user_id')->distinct()->get();
GroupBy
$users = UserLog::where('created_at', '>=', Carbon::now()->subDays(30))
->groupBy('user_id')->get();
Working with Collections
The results from distinct()
and groupBy()
queries are returned as collections, enabling the use of Laravel's rich collection methods for further manipulation or processing.
// Loading related user data from the distinct user_ids
$users = UserLog::where('created_at', '>=', Carbon::now()->subDays(30))
->distinct()->get('user_id');
return $users->load('user');
Multiple Fields Aggregation
The distinct()
and groupBy()
methods can be used to retrieve unique values of multiple fields.
$users = UserLog::where('created_at', '>=', Carbon::now()->subDays(30))
->distinct()->get(['user_id', 'log_title']);
Ordering by Aggregation Count
Sorting the results based on the count of aggregated fields or the distinct values themselves can provide ordered insights into the data.
$users = UserLog::where('created_at', '>=', Carbon::now()->subDays(30))
->distinct()->orderBy('_count')->get('user_id');
The _count
field is an internal field used by the package to reference the count of distinct values
You can also order by the distinct values themselves:
$users = UserLog::where('created_at', '>=', Carbon::now()->subDays(30))
->distinct()->orderBy('user_id')->get('user_id');
Returning Count values with Distinct Results
To include the count of distinct values alongside the results, use distinct(true)
. This can be invaluable for analytics and reporting purposes.
This can only be achieved using the distinct()
method
$users = UserLog::where('created_at', '>=', Carbon::now()->subDays(30))
->distinct(true)->orderByDesc('_count')->get('user_id');