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

Here are different ways that can give you distinct values for non-key column fields in Laravel. Assume we have created a table named students with the following query

CREATE TABLE students( 
   id          INTEGER        NOT NULL      PRIMARY KEY, 
   name        VARCHAR(15)    NOT NULL, 
   mail        VARCHAR(20)    NOT NULL, 
   created_at  VARCHAR(27), 
   updated_at  VARCHAR(27), 
   address     VARCHAR(30)    NOT NULL 
);

And populated it as shown below

+----+---------------+------------------+-----------------------------+-----------------------------+---------+ 
| id |          name |            email |           created_at        |       updated_at            | address | 
| 1  | Siya  Khan    | siya@gmail.com   | 2022-05-01T13:45:55.000000Z | 2022-05-01T13:45:55.000000Z | Xyz     | 
| 2  | Rehan Khan    | rehan@gmail.com  | 2022-05-01T13:49:50.000000Z | 2022-05-01T13:49:50.000000Z | Xyz     | 
| 3  | Rehan Khan    | rehan@gmail.com  |           NULL              | NULL                        | testing | 
| 4  | Rehan         | rehan@gmail.com  |           NULL              | NULL                        | abcd    |
+----+---------------+------------------+-----------------------------+-----------------------------+---------+

Using Model Eloquent

Eloquent is a new object-relational mapper (ORM) that helps to interact with the database. With Eloquent each table has a mapping Model that takes care of all the operations on that table.

Model in Laravel represents the table in the database. For example, if you have table customers, the model name will be customer, for users, it will be user, employees it will be an employee. The table name has to be plural and the model name has to be singular. This is a pattern followed, but that does not stop you from using the naming convention of your choice for table name and model name.

Example 

Make use of the student model inside your controller as shown in the example below −

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\Student;

class StudentController extends Controller {
   public function index() {
      $student = Student::select('name')->distinct()->get()->toArray();
       print_r($student);
   }
}

In the above example, the distinct() method is used to get the distinct values from the “students” table.

Output

The output for the above query is as shown below −

Array(
   [0] => Array(
      [name] => Siya Khan
   )
   [1] => Array(
      [name] => Rehan Khan
   )
   [2] => Array(
      [name] => Rehan
   )
)

Using the distinct() function

The distinct() method will return the unique values from the table. The SQL query for it is as follows −

SELECT distinct name from students;

You can also make use of distinct() method on model eloquent as shown below −

Example 

namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\Student;

class StudentController extends Controller {
   public function index() {
      $student = Student::distinct()->get(['name'])->toArray();
      print_r($student);
   }
}

Outout

The output of the above code is −

Array(
   [0] => Array(
      [name] => Rehan
   )
   [1] => Array(
      [name] => Rehan Khan
   )
   [2] => Array(
      [name] => Siya Khan
   )
)

Using the groupby() method

The groupby() method helps to group the values based on the field name given. The syntax to use groupby is as follows −

odel::select('fieldname')->groupBy('fieldname')

The SQL query is as follows −

 
SELECT name FROM students GROUP BY name;

Example 

You can also make use of groupby() method as shown below −

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\Student;

class StudentController extends Controller {
   public function index() {
      $student = Student::select('name')->groupBy('name')->get()->toArray();
      print_r($student);
   }
}

Output

The output of the above code is −

Array ( 
   [0] => Array ( 
      [name] => Rehan 
   )
   [1] => Array ( 
      [name] => Rehan Khan 
   )
   [2] => Array ( 
      [name] => Siya Khan 
   )

Using the unique() method

The unique() method will return the unique values present in the table. The syntax for using the unique() method is as follows −

Model::all()->unique('fieldname')

Example

The unique() method as shown in the example below −

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\Student;

class StudentController extends Controller {
   public function index() {
      $student = Student::all()->unique('name')->toArray();
      print_r($student);
   }
}

Output

The output of the above code is as follows −

Array(
   [0] => Array(
      [id] => 1
      [name] => Siya Khan
      [email] => siya@gmail.com
      [created_at] => 2022-05-01T13:45:55.000000Z
      [updated_at] => 2022-05-01T13:45:55.000000Z
      [address] => Xyz
   )

   [1] => Array(
      [id] => 2
      [name] => Rehan Khan
      [email] => rehan@gmail.com
      [created_at] => 2022-05-01T13:49:50.000000Z
      [updated_at] => 2022-05-01T13:49:50.000000Z
      [address] => Xyz
   )

   [3] => Array(
      [id] => 4
      [name] => Rehan
      [email] => rehan@gmail.com
      [created_at] =>
      [updated_at] =>
      [address] => abcd
   )
)

Example 

Using DB Facade

Let us use DB Facade inside the controller as shown below. Will make use of distinct() method to get distinct names from students table.

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use DB;

class StudentController extends Controller{
   public function index() {
      $stdDetails = DB::table('students')->distinct()->get(['name'])->toArray();
      print_r($stdDetails);
   }
}

Output

The output of the above code is −

Array ( 
   [0] => stdClass Object ( 
      [name] => Siya Khan 
   )
   [1] => stdClass Object( 
      [name] => Rehan Khan 
   )
   [2] => stdClass Object ( 
      [name] => Rehan 
   )
)

Example 

Using the groupBy() method on the students table. The example is as follows −

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use DB;

class StudentController extends Controller{
   public function index() {
      $stdDetails = DB::table('students')->select('name')->groupBy('name')->get()->pluck('name')->all();
      print_r($stdDetails);
   }
}

Output

The output of the above code is as follows

Array(
   [0] => Rehan
   [1] => Rehan Khan
   [2] => Siya Khan
)