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

User · Answer

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.

User · Answer

Note that groupBy as used above won t work for postgres   Using distinct is probably a better option - e g    users   User  query  - gt distinct  - gt get      If you use query you can select all the columns as requested

User · Answer

users   User  select  column1    column2    column3  - gt distinct  - gt get    retrieves all three coulmns for distinct rows in the table   You can add as many columns as you wish

User · Answer

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- gt id          - gt select   from user    to user            - gt selectRaw  MAX created at  AS last date           - gt groupBy   from user    to user            - gt orderBy  last date    DESC           - gt get

User · Answer

users   Users  all  - gt unique  name

User · Answer

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  - gt unique  name     or     Get all  amp  latest users with unique name  User  latest  - gt get  - gt 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  - gt distinct  name  - gt get

User · Answer

You should use groupby  In Query Builder you can do it this way    users   DB  table  users               - gt select  id   name    email               - gt groupBy  name               - gt get

User · Answer

Get unique value for table  add new videos  column name  project id   project id   DB  table  add new videos  - gt distinct  - gt get   project id

User · Answer

I found this method working quite well  for me  to produce a flat array of unique values    uniqueNames   User  select  name  - gt distinct  - gt pluck  name  - gt toArray      If you ran - gt toSql   on this query builder  you will see it generates a query like this   select distinct  name  from  users    The - gt pluck   is handled by illuminate collection lib  not via sql query

User · Answer

users   User  all    foreach  users- gt unique  column name   as  user       code here

User · Answer

In Eloquent you can also query like this    users   User  select  name  - gt distinct  - gt get

User · Answer

Though I am late to answer this  a better approach to get distinct records using Eloquent would be   user names   User  distinct  - gt get   name

User · Answer

in eloquent you can use  this   users   User  select  name  - gt groupBy  name  - gt get  - gt 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

User · Answer

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           - gt select  id   name    email           - gt get     foreach  users- gt 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           - gt select  id   name    email           - gt get           - gt keyBy  name      The keyBy also allows you to add a call back function for more complex things           users   DB  table  users           - gt select  id   name    email           - gt get           - gt keyBy function  user                 return  user- gt name    -     user- gt id                 If you have to iterate over large collections  adding a key to it solve the performance issue

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

A. Records in DB

B. Desired Grouped result

Examples related to laravel

Examples related to eloquent

Examples related to builder