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

Question

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?

score 154 · Accepted Answer · answered Aug 10 '14 at 13:08

154

You should use groupby. In Query Builder you can do it this way:

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

answered Aug 10 '14 at 13:08

Marcin Nabiałek

109,655
42
258
291

2

I have a table "messages" (used for a chat), and I want to get the latest message that the authenticated user has received from each conversation. Using groupBy I can get just one message, but I get the first, and I need the last message. Seems that orderBy doesn't work. – JCarlosR Jul 22 '16 at 03:32
21

if you dont' want to apply any math operation such as SUM, AVG, MAX and so on "group by" is not the correct answer (you can use it, but you shouldn't use it). You should use distinct. In MySQL you can use DISTINCT for a column and add more columns to the resultset: "SELECT DISTINCT name, id, email FROM users". With eloquent you can do this with $this->select(['name', 'id', 'email'])->distinct()->get(); – Sergi Feb 10 '17 at 16:44
2

when i add a `where()` it breaks, how can i fix this? – t q Mar 14 '18 at 23:26
1

when I use this it shows 'id' and 'email' isn't in groupBy() i need to use groupBy('id','name', 'email'). Which is not useful. – Haque Aug 11 '18 at 19:40
2

Something to note: `group by` is not the same as `distinct`. `group by` will change the behavior of aggregate functions like `count()` inside the SQL query. `distinct` will not change the behavior of such functions, so you get different results depending on which one you use. – Skeets Jun 05 '20 at 07:29

Pathros · Answer 2 · 2022-05-26T21:29:43.310

150

In Eloquent you can also query like this:

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

edited May 26 '22 at 21:29

answered Sep 30 '15 at 16:25

Pathros

10,042
20
90
156

18

Question specifically asks `Note that I am not fetching that column only, it is in conjunction with other column values` – Hirnhamster Oct 23 '15 at 11:20
23

@Hirnhamster : Okkei. However, this answer is still useful for others passing by ... – Pathros Jan 30 '17 at 15:06
4

Not useful for me, im specifically looking what the OP is. Its not an easy find. – blamb Dec 28 '17 at 03:14
2

`$users = User::select('column1', 'column2', 'column3')->distinct()->get();` – Mark-Hero Sep 07 '18 at 15:53
Also you need `->orderBy('name')` if you want to use this with `chunk`. – Chibueze Opata Jun 08 '20 at 16:43
2

this should be the accepted answer imo – Tim Bogdanov May 26 '22 at 21:18

Salar · Answer 3 · 2016-10-12T22:03:36.170

32

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

edited Oct 12 '16 at 22:03

answered May 03 '15 at 19:18

Salar

5,305
7
50
78

5

How does this work? Is the groupBy actually fetching distinct user names? Could you please explain just a bit more how this answers op's problem? – Félix Adriyel Gagnon-Grenier May 03 '15 at 20:09
1

Yes 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. – Salar May 04 '15 at 05:42
I see.. then, how is this answer any different from Marcin's one? Having a different answer is ok, as long as you can explain how it is different and what flaw it solves :) Don't bother answering in comments, please directly edit your question with relevant information. – Félix Adriyel Gagnon-Grenier May 04 '15 at 15:23
2

the result is same, it depends on your project to use ORM or use query builder, if you are using one of them, then its better to stick with that one. that's why I answered your question in a different way. – Salar May 04 '15 at 16:29
Well, I have this issue where, if you now want to check if an item exists by using the `in_array()` function, it never works. To fix it, I tried `->lists()` instead (Version 5.2). So, `$users = User::select('name')->groupBy('name')->lists('name');` worked fine for php's `in_array()`; – Pathros Nov 28 '16 at 15:43
When strict is marked as true in Laravel application, most of modern DB doesn't work Group By without having Aggregate functions in the select statement. – rsakhale May 24 '19 at 13:37

score 27 · Answer 4 · answered Nov 06 '17 at 04:35

27

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

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

answered Nov 06 '17 at 04:35

rsakhale

1,018
13
26

Ok, you've added value by showing us that one can also pass params of field names to the `get()` method (and I've tested also to the `first()` method), which is equivalent to using the `select()` method as shown in a few answers here. Though somehow `groupBy` still seems to score the highest. However this would be truly distinct if that is the only column I'm selecting. – gthuo Nov 07 '17 at 05:04
Performance wise, distinct is going to score over groupBy, because the records will be selected at first in the SQL Engine unlike in Group By the engine first selects all records and then group's by.. – rsakhale Nov 10 '17 at 09:59
1

`$user_names = User::distinct()->count(['name']);` also works – Bira Feb 07 '20 at 08:47

Robby Alvian Jaya Mulia · Answer 5 · 2020-01-30T10:58:21.350

23

**

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();

edited Jan 30 '20 at 10:58

answered Dec 17 '19 at 08:49

Robby Alvian Jaya Mulia

1,286
14
15

People using this solution without knowing what they are actually doing, surprising!!! – ssi-anik May 15 '22 at 16:57
`distinct()` doesn't take parameters – brad Nov 09 '22 at 21:47

Jed Lynch · Answer 6 · 2019-10-13T08:18:41.397

18

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.

edited Oct 13 '19 at 08:18

answered Jun 11 '18 at 18:26

Jed Lynch

1,998
18
14

You're correct and is exactly the issue I'm up against... however waiting until the query is executed and perform the action in the collection is also not ideal. In particular if you were relying on pagination, your operation would be after the pagination calculation and the items per page would be wrong. Also, the DB is better suited to operate on large data chunks rather the retrieving and processing it in code. For small data chunks it would be less of an issue – ChronoFish Oct 11 '19 at 16:12
You have a good point. This method may not be great performance wise on large collections, and this won't work for pagination. I think there is a better answer that what I have. – Jed Lynch Oct 13 '19 at 04:07

score 7 · Answer 7 · answered Jan 22 '17 at 15:14

7

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.

answered Jan 22 '17 at 15:14

jec006

91
1
3

score 6 · Answer 8 · answered Sep 07 '18 at 15:51

6

$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.

answered Sep 07 '18 at 15:51

Mark-Hero

205
3
7

score 5 · Answer 9 · answered Sep 17 '19 at 23:31

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).

score 4 · Answer 10 · answered Nov 14 '20 at 18:36

4

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

answered Nov 14 '20 at 18:36

Nazmul Haque

720
8
13

Alex Christodoulou · Answer 11 · 2020-06-17T16:49:41.220

3

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()

edited Jun 17 '20 at 16:49

answered Jun 17 '20 at 16:41

Alex Christodoulou

2,873
2
16
18

score 2 · Answer 12 · answered Dec 28 '21 at 19:22

2

Here are 3 ways I have tested that will give same result:

User::distinct()->get(['name'])->pluck('name');

User::select('name')->distinct()->pluck('name')->all();

DB::table('users')->select('name')->groupBy('name')->get()->pluck('name')->all();

answered Dec 28 '21 at 19:22

Abms

31
1

score 1 · Answer 13 · answered Nov 13 '22 at 19:40

In my case (with Laravel 9), I need:-

to use where clause.
to get complete row.
to get unique entries for specific column.

As more complex query, but still, I have an easy approach. May be that help you also.

User::where('id',auth()->user()->id)->distinct('name')->get();

score 0 · Answer 14 · answered Aug 24 '20 at 07:02

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.

score 0 · Answer 15 · answered May 18 '23 at 12:54

This worked for me (my case is not on User but same method).

User::selectRaw('DISTINCT `name`')->addSelect('users.*')->get();

However the selectRaw is ignored when pagination calculates the total, by doing SELECT count(*) AS aggregate.

This seems quite a common use case, any ideas about why this is not possible in Eloquent/Laravel?

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

15 Answers15

Tested for Laravel 5.8

A. Records in DB

B. Desired Grouped result

Linked