Laravel 5 Multiple Where Conditions Query

This is an article written on how to show in Laravel 5 especially but it seems it will work across any Laravel version about using multiple where conditions in a query generated using DB Facade class. As we already knew, DB Facade class is a special class used to generate query using several methods available so that the query itself can be tailored based on needs.

One of those needs which is particularly utilized in an SQL query for extracting records from tables is adding or attaching multiple where conditions. It is obviously happened in a certain conditions where there are more than one filter described to acquire the result. So, each of the filter must be described in a where condition clause.

Normally, the where condition clause is only specified one times with another clause of ‘and’ attached to add more filter to the query generated. Since it is not using a simple and normal SQL or a query language executed, there is an adjustment made to create or to design the query language or the SQL which is going to be generated using a DB Facade class.

Below is the description of two table which is joined with several filter using multiple clauses :

The first table named ‘user’ and it can be described as follows :

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>

Another table which is going to be joined is shown below :

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>

Based on those two tables, below is the query generated using multiple where clause conditions. The conditions are getting a specific user identified by an ‘id’ and that user have their own role available in the user_role table :

$available_role = DB::table('users')
...
...
->where('users.id','=',$id_user)
->where('id_role','<>',null)

Instead of using ‘and’ clause to add filter for another different condition just add ‘where’ clause to specify it where it can be shown below :

->where('users.id','=',$id_user)
->where('id_role','<>',null)