How to alias a table in Laravel Eloquent queries using Query Builder?

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.

Assume we have already created a table with the name student with the following contents −

+----+---------------+------------------+-----------------------------+-----------------------------+---------+------+
| id | name          | email            | created_at                  | updated_at                  | address | age  |
+----+---------------+------------------+-----------------------------+-----------------------------+---------+------+
| 1  | Siya Khan     | siya@gmail.com   | 2022-05-01T13:45:55.000000Z | 2022-05-01T13:45:55.000000Z | Xyz     | 20   |
| 2  | Rehan Khan    | rehan@gmail.com  | 2022-05-01T13:49:50.000000Z | 2022-05-01T13:49:50.000000Z | Xyz     | 18   |
| 3  | Rehan Khan    | rehan@gmail.com  | NULL                        | NULL                        | testing | 20   |
| 4  | Rehan         | rehan@gmail.com  | NULL                        | NULL                        | abcd    | 15   |
| 5  | Nidhi Agarwal | nidhi@gmail.com  | NULL                        | NULL                        | abcd    | 20   |
| 6  | Ashvik Khanna | ashvik@gmail.com | NULL                        | NULL                        | oooo    | 16   |
| 7  | Viraj Desai   | viraj@gmail.com  | NULL                        | NULL                        | test    | 18   |
| 8  | Priya Singh   | priya@gmail.com  | NULL                        | NULL                        | test123 | 20   |
+----+---------------+------------------+-----------------------------+-----------------------------+---------+------+
8 rows in set (0.00 sec)

Example

In Laravel aliases on a table can be created using the "as" keyword. Following is an example to do so

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

class StudentController extends Controller {
   public function index() {
      echo $student = Student::from( 'students as std' )
      ->orderBy('std.name', 'ASC')
      ->orderBy('std.email', 'ASC')
      ->get();
   }
}

Output

The output of the above code is as follows

[{"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"},{"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"}]

Example 1

Let us now test alias using DB facade

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

//use App\Models\Student;
use DB;

class StudentController extends Controller {
   public function index() {
      $users = DB::select('select * from students as std');
      print_r($users);
   }
}

Output

Array (
   [0] => stdClass Object(
      [id] => 1
      [name] => Siya Khan
      [email] => siya@gmail.com
      [created_at] => 2022-05-01 13:45:55
      [updated_at] => 2022-05-01 13:45:55
      [address] => Xyz
   )
   [1] => stdClass Object(
      [id] => 2
      [name] => Rehan Khan
      [email] => rehan@gmail.com
      [created_at] => 2022-05-01 13:49:50
      [updated_at] => 2022-05-01 13:49:50
      [address] => Xyz
   )
)

Example 2

Following is another way to create an alias of a table using DB façade −

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

//use App\Models\Student;
use DB;

class StudentController extends Controller {
   public function index() {
      $users = DB::table('students as std')
      ->get(array('std.name as std_name'));
      print_r($users);
   }
}

Output

The output of the above program is −

Illuminate\Support\Collection Object (
   [items:protected] => Array(
      [0] => stdClass Object(
         [std_name] => Siya Khan
      )
      [1] => stdClass Object(
         [std_name] => Rehan Khan
      )
   )
   [escapeWhenCastingToString:protected] =>
)

Updated on: 29-Aug-2022

7K+ Views

Related Articles

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements