WHERE part is separated because it can be combined with Select,
Update and Delete query
WHERE can be initialized and because of that, you can build
complex WHERE statements. Here you'll find a lot of examples and various combinations on how to use it.
The simplest where statement is when you use it on
Select. Same is for
$select = new Select('user'); $select->where('id', 5); $update = new Update('user'); $update->set('first_name', 'John'); $update->where('id', 5); $update->exec();
The two examples above are pretty straight forward.
where() method is pretty powerful because it accepts various range
of parameter combinations. It's made to feel straight forward when building query. So here are some combinations you can
where(column, value)- if you pass two parameters, then first parameter is the column name which has to have the given value - by default,
=will be used as operator
where(column, '!=', value)- if you need to put the operator, simply put it between first and third parameter. You can use other operators like
ILIKE- anything that your database accepts
where(instance of Where)- you can pass instance of
Whereon the first place to make nested WHERE statement
where(Db::expr("anything = 'you want'"))- you can pass instance of
Exprin which case nothing will be parsed or escaped. In this case, you have to handle everything by yourself. This is not recommended so use it only if there's no other way.
If you call
where() method twice, it'll be linked with
AND. To link it with
OR, simply use method that starts
or methods accepts the same parameters as their opposite version.
$select = new Select('user'); $select->where('id', 5)->orWhere('id', 7);
Other WHERE methods usually have their negation version, like
not in or
not null. Here they are.
$select->whereBetween('id', 5, 8); $select->orWhereBetween('id', 5, 8); $select->whereNotBetween('id', 5, 8); $select->orWhereNotBetween('id', 5, 8);
$select->whereIn('id', [5, 8, 9]); $select->orWhereIn('id', [5, 8, 9]); $select->whereNotIn('id', [5, 8, 9]); $select->orWhereNotIn('id', [5, 8, 9]);
This is alias to
where(column, 'LIKE', value). To use PostgreSQL's ILIKE, you'll have to pass that operator manually
$select->whereLike('first_name', 'John'); $select->orWhereLike('first_name', '%John%'); $select->whereNotLike('first_name', 'John'); $select->orWhereNotLike('first_name', '%John%');
This is useful when selecting records with NULL-value columns.
null methods accepts just column name as parameter.
$select->whereNull('age'); $select->orWhereNull('age'); $select->whereNotNull('age'); $select->orWhereNotNull('age');
Nested Where Statements
To nest two or more
Where statements, simply pass instance of
Where to another
where() method. Let's create
SELECT query that selects users where first name is
John and age is
25 or where last name is
Smith and age is
$select = new Select('user'); $where1 = Where::init()->where('first_name', 'John')->where('age', 25); $where2 = Where::init()->where('last_name', 'Smith')->where('age', 30); $select->where($where1)->orWhere($where2);
The query will look like this:
SELECT * FROM user WHERE ((first_name = 'John') AND (age = 25)) OR ((last_name = 'Smith') AND (age = 30))