yii2 Working with Databases More condition checking using where()

Example

Multiple conditions can be written using where() method as given below.

// Creates a new \yii\db\Query() object
$query = new \yii\db\Query();
$rows = $query->select(['emp_name','emp_salary']) 
        ->from('employee')
        ->where(['emp_name' => 'Kiran', 'emp_salary' => 25000]) // Specify multiple conditions
        ->one(); // Returns the first row of the result

The above code will fetch an employee having the name kiran and salary 25000. If multiple employees are satisfying the above condition, the call one() makes sure that only the first result is fetched. To fetch all results you should use all().

Note that if you use all() the result will always be an array; Even if there is only one or zero results. This array contains all results as arrays or is empty when no records match. The call one() will return the resulting array directly or false if the query doesn't return anything.

The equivalent code in sql is given below.

select emp_name, emp_salary from employee where emp_name = 'Kiran' and emp_salary = 25000 limit 1;

An alternative way of writing the above query in Yii2 is given below.

$rows = $query->select(['emp_name', 'emp_salary']) 
    ->from('employee')
    ->where(['emp_name' => 'Kiran'])
    ->andWhere(['emp_salary' => 25000])
    ->one();

Additional set of conditions can be specified using andWhere. This will be useful if we need to add additional condition checking to the query later.

Yet another way to specify multiple conditions is by making use of operator format of where() method.The above query can also be written as given below.

 $rows = $query->select(['emp_name','emp_salary']) 
    ->from('employee')
    ->where(['and', 'emp_name="kiran"', 'emp_salary=25000'])        
    ->one();

Here we specify the operator 'and' as the first element in the array. Similarly we can also use 'or', 'between', 'not between', 'in', 'not in', 'like', 'or like', 'not like', 'or not like', 'exists', 'not exists', '>', '<=' etc as operators.

Examples of using 'in' and 'like'

Suppose we need to find the employees having salaries 20000, 25000 and 50000. In normal sql we would write the query as

select * from employee where salary in (20000,25000,50000)

In Yii2 we can write this as given below.

$rows = $query->from('employee')
        ->where(['emp_salary' => [20000,25000,50000]]) 
        ->all();

Another way of specifying the same condition is

$rows = $query->from('employee')
    ->where(['in', 'emp_salary', [20000,25000,50000]]) // Making use of operator format of where() method
    ->all();

Similarly 'not in' can be specified instead of 'in' if we want to get all employees not having salaries 20000, 25000 and 50000.

Now let us see some examples of using 'like' inside where() condition. Suppose we need to find all employees having the string 'gopal' in their name. The names can be venugopal, rajagopal, gopalakrishnan etc. The sql query is given below.

select * from employee where emp_name like '%gopal%'

In Yii2 we will write this as

 $rows = $query->from('employee')
        ->where(['like', 'emp_name', 'gopal']) // Making use of operator format of where() method
        ->all();

If we need to find all employees having the string 'gopal' and 'nair' in their name. We can write as

   $rows = $query->from('employee')
        ->where(['like', 'emp_name', ['gopal','nair']]) // Making use of operator format of where() method
        ->all();

This would evaluate as

select * from employee where emp_name like '%gopal%' and '%nair%'

Similarly we can use 'not like' to indicate all employees not having the string 'gopal' and 'nair' in their names.