Distinct and GroupBy

In the Laravel-OpenSearch 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 OpenSearch's term aggregation capabilities, offering powerful data analysis tools directly within the Eloquent model.

For this OpenSearch 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');

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.

$users = UserLog::where('created_at', '>=', Carbon::now()->subDays(30))
    ->distinct(true)->orderByDesc('_count')->get('user_id');