Laravel 5 Using Left Join Query

Another form of query which is specifically available upon retrieving it in a web-based application powered by Laravel 5 or any kinds of version Laravel had because it seems as a general feature available is a query using left join clause.

The left join clause is used generally to join two tables where the data retrieved will mainly taken on the first table added with the data on the second table that match the criteria specified that exist between two tables.

Normally, the criteria is represented by a single column id which has the same concept, criteria or meaning in both tables. In the context of this article as an example of scenario, that column will be represented with an id of a user in table ‘user’ which is also exist in another table called ‘role’ which is also point out the id of a ‘user’. Below is the description of those two tables :

Table ‘user’ :

mysql> desc users;
+----------------+--------------+------+-----+-------------------+-----------------------------+
| Field          | Type         | Null | Key | Default           | Extra |
+----------------+--------------+------+-----+-------------------+-----------------------------+
| id             | int(5)       | NO | PRI | NULL                | auto_increment |
| username       | varchar(150) | NO |     | NULL                | |
| email          | varchar(255) | NO |     | NULL                | |
| password       | varchar(255) | NO |     | NULL                | |
| remember_token | varchar(100) | NO |     | NULL                | |
| created_at     | timestamp    | NO |     | CURRENT_TIMESTAMP   | | on update CURRENT_TIMESTAMP     |
| updated_at     | timestamp    | NO |     | 0000-00-00 00:00:00         |              |
| name           | varchar(255) | NO |     | NULL                | |
| level          | varchar(20)  | NO |     | operator            | |
| id_server      | int(11)      | NO | MUL | NULL                | |
| id_app         | int(11)      | NO | MUL | NULL                | |
| id_db          | int(11)      | NO | MUL | NULL                | |
+----------------+--------------+----+-----+---------------------+-----------------------------+
12 rows in set (0,20 sec)
mysql>

Table ‘user_role’ :

mysql> desc user_role;
+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| id      | int(11) | NO   | PRI | NULL    | auto_increment |
| id_user | int(11) | NO   |     | NULL    |                |
| id_role | int(11) | NO   |     | NULL    |                |
+---------+---------+------+-----+---------+----------------+
3 rows in set (0,00 sec)
mysql>

In both tables, in ‘user’ and ‘user_role’ table, there is the similar column which has the same concept or value, it is the ‘id’ column in table ‘user’ and also ‘id_user’ in table ‘user_role’. To select all data in user where the user itself has a certain role, the following query which involved left join operation can be assembled :

$available_role = DB::table('users')
->leftJoin('role', 'role.id_role', '=', 'user_role_server.id_role')
->where('users.id', '=', $id_user)
->get();

The above DB class which is provided in Laravel web-based application framework is providing a method to perform left join operation. The operation itself is executed by doing left join table users with table role based on the primary key id column of user table.