[laravel] How to get distinct values for non-key column fields in Laravel?

This might be quite easy but have no idea how to.

I have a table that can have repeated values for a particular non-key column field. How do I write a SQL query using Query Builder or Eloquent that will fetch rows with distinct values for that column?

Note that I am not fetching that column only, it is in conjunction with other column values, so distinct() might not really work. So that question basically can be how to specify the column that I want to be distinct in a query now that distinct() accepts no parameters?

This question is related to laravel eloquent builder

The answer is


$users = Users::all()->unique('name');

// Get unique value for table 'add_new_videos' column name 'project_id'
$project_id = DB::table('add_new_videos')->distinct()->get(['project_id']);

in eloquent you can use this

$users = User::select('name')->groupBy('name')->get()->toArray() ;

groupBy is actually fetching the distinct values, in fact the groupBy will categorize the same values, so that we can use aggregate functions on them. but in this scenario we have no aggregate functions, we are just selecting the value which will cause the result to have distinct values


I had the same issues when trying to populate a list of all the unique threads a user had with other users. This did the trick for me

Message::where('from_user', $user->id)
        ->select(['from_user', 'to_user'])
        ->selectRaw('MAX(created_at) AS last_date')
        ->groupBy(['from_user', 'to_user'])
        ->orderBy('last_date', 'DESC')
        ->get()

I found this method working quite well (for me) to produce a flat array of unique values:

$uniqueNames = User::select('name')->distinct()->pluck('name')->toArray();

If you ran ->toSql() on this query builder, you will see it generates a query like this:

select distinct `name` from `users`

The ->pluck() is handled by illuminate\collection lib (not via sql query).


Grouping by will not work if the database rules don't allow any of the select fields to be outside of an aggregate function. Instead use the laravel collections.

$users = DB::table('users')
        ->select('id','name', 'email')
        ->get();

foreach($users->unique('name') as $user){
  //....
}

Someone pointed out that this may not be great on performance for large collections. I would recommend adding a key to the collection. The method to use is called keyBy. This is the simple method.

     $users = DB::table('users')
        ->select('id','name', 'email')
        ->get()
        ->keyBy('name');

The keyBy also allows you to add a call back function for more complex things...

     $users = DB::table('users')
        ->select('id','name', 'email')
        ->get()
        ->keyBy(function($user){
              return $user->name . '-' . $user->id;
         });

If you have to iterate over large collections, adding a key to it solve the performance issue.


**

Tested for Laravel 5.8

**

Since you wanna get all columns from the table, you can collect all of the data and then filter it using Collections function called Unique

// Get all users with unique name
User::all()->unique('name')

or

// Get all & latest users with unique name 
User::latest()->get()->unique('name')

For more information you can check Laravel Collection Documentations

EDIT: You might have issue with perfomance, by using Unique() you'll get all data first from User table, and then Laravel will filter it. This way isn't good if you have lots of Users data. You can use query builder and call each fields that you wanna use, example:

User::select('username','email','name')->distinct('name')->get();

Note that groupBy as used above won't work for postgres.

Using distinct is probably a better option - e.g. $users = User::query()->distinct()->get();

If you use query you can select all the columns as requested.


For those who like me doing same mistake. Here is the elaborated answer Tested in Laravel 5.7

A. Records in DB

UserFile::orderBy('created_at','desc')->get()->toArray();

Array
(
    [0] => Array
        (
            [id] => 2073
            [type] => 'DL'
            [url] => 'https://i.picsum.photos/12/884/200/300.jpg'
            [created_at] => 2020-08-05 17:16:48
            [updated_at] => 2020-08-06 18:08:38
        )

    [1] => Array
        (
            [id] => 2074
            [type] => 'PROFILE'
            [url] => 'https://i.picsum.photos/13/884/200/300.jpg'
            [created_at] => 2020-08-05 17:20:06
            [updated_at] => 2020-08-06 18:08:38
        )

    [2] => Array
        (
            [id] => 2076
            [type] => 'PROFILE'
            [url] => 'https://i.picsum.photos/13/884/200/300.jpg'
            [created_at] => 2020-08-05 17:22:01
            [updated_at] => 2020-08-06 18:08:38
        )

    [3] => Array
        (
            [id] => 2086
            [type] => 'PROFILE'
            [url] => 'https://i.picsum.photos/13/884/200/300.jpg'
            [created_at] => 2020-08-05 19:22:41
            [updated_at] => 2020-08-06 18:08:38
        )
)

B. Desired Grouped result

UserFile::select('type','url','updated_at)->distinct('type')->get()->toArray();

Array
(
    [0] => Array
        (
            [type] => 'DL'
            [url] => 'https://i.picsum.photos/12/884/200/300.jpg'
            [updated_at] => 2020-08-06 18:08:38 
        )

    [1] => Array
        (
            [type] => 'PROFILE'
            [url] => 'https://i.picsum.photos/13/884/200/300.jpg'
            [updated_at] => 2020-08-06 18:08:38
        )
)

So Pass only those columns in "select()", values of which are same. For example: 'type','url'. You can add more columns provided they have same value like 'updated_at'.

If you try to pass "created_at" or "id" in "select()", then you will get the records same as A. Because they are different for each row in DB.


$users = User::select('column1', 'column2', 'column3')->distinct()->get(); retrieves all three coulmns for distinct rows in the table. You can add as many columns as you wish.


Though I am late to answer this, a better approach to get distinct records using Eloquent would be

$user_names = User::distinct()->get(['name']);

$users = User::all();
foreach($users->unique('column_name') as $user){
    code here..
}

In Eloquent you can also query like this:

$users = User::select('name')->distinct()->get();


Examples related to laravel

Parameter binding on left joins with array in Laravel Query Builder Laravel 4 with Sentry 2 add user to a group on Registration Target class controller does not exist - Laravel 8 Visual Studio Code PHP Intelephense Keep Showing Not Necessary Error The POST method is not supported for this route. Supported methods: GET, HEAD. Laravel How to fix 'Unchecked runtime.lastError: The message port closed before a response was received' chrome issue? Post request in Laravel - Error - 419 Sorry, your session/ 419 your page has expired Expected response code 250 but got code "530", with message "530 5.7.1 Authentication required How can I run specific migration in laravel Laravel 5 show ErrorException file_put_contents failed to open stream: No such file or directory

Examples related to eloquent

Eloquent: find() and where() usage laravel How to select specific columns in laravel eloquent Laravel Eloquent where field is X or null Laravel Eloquent limit and offset laravel collection to array Eloquent get only one column as an array Laravel 5.2 - Use a String as a Custom Primary Key for Eloquent Table becomes 0 Laravel 5.2 - pluck() method returns array Eloquent ORM laravel 5 Get Array of ids eloquent laravel: How to get a row count from a ->get()

Examples related to builder

How to get distinct values for non-key column fields in Laravel? Android AlertDialog Single Button Dialog throwing "Unable to add window — token null is not for an application” with getApplication() as context When would you use the Builder Pattern?