v2.7.4

New Feature

Distinct and GroupBy

This section covers the implementation of distinct() and groupBy() methods in the Elasticsearch Eloquent model. These methods are interchangeable and use term aggregation under the hood.

This tends to be a core use case for Elasticsearch, for example, to get all the unique user_ids of the users who have been logged in the last 30 days:

Basic Usage

• Distinct:

// Unique user_ids of users logged in the last 30 days
$users = UserLog::where('created_at', '>=', Carbon::now()->subDays(30))->distinct()->get('user_id');
//or:
$users = UserLog::where('created_at', '>=', Carbon::now()->subDays(30))->select('user_id')->distinct()->get();

• GroupBy:

// Equivalent to the above distinct query
$users = UserLog::where('created_at', '>=', Carbon::now()->subDays(30))->groupBy('user_id')->get();

Working with Collections

• The results from these queries are returned as collections, allowing use of standard collection methods.
• Example of loading related user data:

$users = UserLog::where('created_at', '>=', Carbon::now()->subDays(30))->distinct()->get('user_id');
return $users->load('user');

Multiple Fields Aggregation

• You can pass multiple fields to perform term aggregation.
• Example:

$users = UserLog::where('created_at', '>=', Carbon::now()->subDays(30))->distinct()->get(['user_id', 'log_title']);
/** returns: 
{
    "user_id": "1",
    "log_title": "LOGGED_IN"
},
{
    "user_id": "2",
    "log_title": "LOGGED_IN"
},
{
    "user_id": "2",
    "log_title": "LOGGED_OUT"
},
 **/

Ordering by Aggregation Count

• Results can be sorted based on the count of the aggregated field.
• Example of ordering by the most logged users:

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

• Or you can order by the distinct field, example:

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

Returning Count with Distinct Results

• To include the count of distinct values in the results, use distinct(true):

$users = UserLog::where('created_at', '>=', Carbon::now()->subDays(30))->distinct(true)->orderBy('_count')->get(['user_id']);
/** returns: 
{
    "user_id": "5",
    "user_id_count": 65
},
{
    "user_id": "1",
    "user_id_count": 61
},
{
    "user_id": "9",
    "user_id_count": 54
},
 **/

Pagination Support

• The distinct() and groupBy() methods support pagination.
• Example:

$users = UserLog::where('log_title', 'LOGGED_IN')->select('user_id')->distinct()->orderBy('_count')->paginate(20);
//or
$users = UserLog::where('log_title', 'LOGGED_IN')->groupBy('user_id')->orderBy('_count')->paginate(20);

Full Changelog: v2.7.3...v2.7.4