Filters
Learn how to filter records
Filtering records is one of the most important and intensively used operation in SQL. Opis Database aims to make data filtering an easy task for the developers, by implementing a set of targeted methods capable of simplifying the filtering process.
Adding filters
Adding a filtering condition is done by using the where method in conjunction
with one of the following methods: is or eq, isNot or ne, lessThan or lt,
greaterThan or gt, atLeast or gte, atMost or lte, between, notBetween,
in, notIn, like, notLike, isNull and notNull.
The is method
Adds a filtering condition, so that only those records, that have the specified
column’s value equal to a given value, will be added to the result set.
Alternatively, to add this filtering condition, you can use the eq(equal) method,
which is an alias of the is method.
// Select all users that are 18.
$result = $db->from('users')
->where('age')->is(18) //Alternatively: ->where('age')->eq(18)
->select()
->all();
SELECT * FROM `users` WHERE `age` = 18
The isNot method
Adds a filtering condition, so that only those records, that have the specified
column’s value not equal to a given value, will be added to the result set.
Alternatively, to add this filtering condition, you can use the ne(not equal) method,
which is an alias of the isNot method.
// Select all users that are not 18.
$result = $db->from('users')
->where('age')->isNot(18) //Alternatively: ->where('age')->ne(18)
->select()
->all();
SELECT * FROM `users` WHERE `age` != 18
The lessThan method
Adds a filtering condition, so that only those records, that have the specified
column’s value lesser than a given value, will be added to the result set.
Alternatively, to add this filtering condition, you can use the lt(less than) method,
which is an alias of the lessThan method.
// Select all users that are under 18.
$result = $db->from('users')
->where('age')->lessThan(18) //Alternatively: ->where('age')->lt(18)
->select()
->all();
SELECT * FROM `users` WHERE `age` < 18
The greaterThan method
Adds a filtering condition, so that only those, records that have the specified column’s
value greater than a given value, will be added to the result set.
Alternatively, to add this filtering condition, you can use the gt(greater than) method,
which is an alias of the greaterThan method.
// Select all users that are over 18.
$result = $db->from('users')
->where('age')->greaterThan(18) //Alternatively: ->where('age')->gt(18)
->select()
->all();
SELECT * FROM `users` WHERE `age` > 18
The atLeast method
Adds a filtering condition, so that only those records, that have the specified
column’s value greater than or equal to a given value, will be added to the result set.
Alternatively, to add this filtering condition, you can use the gte(greater than or equal) method,
which is an alias of the atLeast method.
// Select all users that are at least 18.
$result = $db->from('users')
->where('age')->atLeast(18) //Alternatively: ->where('age')->gte(18)
->select()
->all();
SELECT * FROM `users` WHERE `age` >= 18
The atMost method
Adds a filtering condition, so that only those records, that have the specified
column’s value lesser than or equal to a given value, will be added to the result set.
Alternatively, to add this filtering condition, you can use the lte(less than or equal) method,
which is an alias of the atMost method.
// Select all users that are at most 18.
$result = $db->from('users')
->where('age')->atMost(18) //Alternatively: ->where('age')->lte(18)
->select()
->all();
SELECT * FROM `users` WHERE `age` <= 18
The between method
Adds a filtering condition, so that only those records, that have the specified column’s value within a given range, will be added to the result set.
// Select all users that are between 18 and 21.
$result = $db->from('users')
->where('age')->between(18, 21)
->select()
->all();
SELECT * FROM `users` WHERE `age` BETWEEN 18 AND 21
The notBetween method
Adds a filtering condition, so that only those records, that don’t have the specified column’s value within a given range, will be added to the result set.
// Select all users that are not between 18 and 21.
$result = $db->from('users')
->where('age')->notBetween(18, 21)
->select()
->all();
SELECT * FROM `users` WHERE `age` NOT BETWEEN 18 AND 21
The in method
Adds a filtering condition, so that only those records, that have the specified column’s value contained within a given set of values, will be added to the result set.
// Select all users that are living in London, New York or Paris.
$result = $db->from('users')
->where('city')->in(['London', 'New York', 'Paris'])
->select()
->all();
SELECT * FROM `users` WHERE `city` IN ("London", "New York", "Paris")
Instead of providing a set of values by passing an array to the in method, you could also
obtain a set of values by using a subquery. To use a subquery, just pass an anonymous function callback
as an argument to the in method, then use the object that will be passed as an argument to your
callback function to build your query.
/**
* Select all users that are living in a city
* which has a population of over 10 millions inhabitants.
*/
$result = $db->from('users')
->where('city')->in(function($query){
$query->from('cities')
->where('population')->atLeast(10000000)
->select('name');
})
->select()
->all();
SELECT * FROM `users`
WHERE `city` IN (SELECT `name` FROM `cities` WHERE `population` >= 10000000)
The notIn method
Adds a filtering condition, so that only those records, that don’t have the specified column’s value contained within a given set of values, will be added to the result set.
// Select all users that are not living in London, New York or Paris.
$result = $db->from('users')
->where('city')->notIn(['London', 'New York', 'Paris'])
->select()
->all();
SELECT * FROM `users` WHERE `city` NOT IN ("London", "New York", "Paris")
As in the case of the in method, you could obtain a set of values to be used for comparison,
by using a subquery.
/**
* Select all users that are not living in a city
* which has a population of over 10 millions inhabitants.
*/
$result = $db->from('users')
->where('city')->notIn(function($query){
$query->from('cities')
->where('population')->atLeast(10000000)
->select('name');
})
->select()
->all();
SELECT * FROM `users`
WHERE `city` NOT IN (SELECT `name` FROM `cities` WHERE `population` >= 10000000)
The like method
Adds a filtering condition, so that only those records, whose specified column’s value match a given pattern, will be added to the result set.
/**
* Select all users that are living in a city
* whose name starts with the letter 'P'.
*/
$result = $db->from('users')
->where('city')->like('P%')
->select()
->all();
SELECT * FROM `users` WHERE `city` LIKE "P%"
The notLike method
Adds a filtering condition, so that only those records, whose specified column’s value don’t match a given pattern, will be added to the result set.
/**
* Select all users that are living in a city
* whose name doesn't starts with the letter 'P'.
*/
$result = $db->from('users')
->where('city')->notLike('P%')
->select()
->all();
SELECT * FROM `users` WHERE `city` NOT LIKE "P%"
The isNull method
Adds a filtering condition, so that only those records, that have the specified column’s
value equal to NULL, will be added to the result set.
// Select all users that do not have a website.
$result = $db->from('users')
->where('website')->isNull()
->select()
->all();
SELECT * FROM `users` WHERE `website` IS NULL
The notNull method
Adds a filtering condition, so that only those records, that have the specified
column’s value not equal to NULL, will be added to the result set.
// Select all users that do have a website.
$result = $db->from('users')
->where('website')->notNull()
->select()
->all();
SELECT * FROM `users` WHERE `website` IS NOT NULL
Multiple conditions
Adding multiple conditions to your query is done by using the andWhere and orWhere methods.
Those methods works exactly as the where method and depending on which method you use,
they will combine with the previous declared condition by using an AND or an OR operator.
To add an additional condition to your query, that combines with the previous declared condition by
using an AND operator, use the andWhere method.
// Select all users that are 18 and are living in London.
$result = $db->from('users')
->where('age')->is(18)
->andWhere('city')->is('London')
->select()
->all();
SELECT * FROM `users` WHERE `age` = 18 AND `city` = "London"
To add an additional condition to your query, that combines with the previous declared condition
by using an OR operator, use the orWhere method.
// Select all users that are either 18 or 21.
$result = $db->from('users')
->where('age')->is(18)
->orWhere('age')->is(21)
->select()
->all();
SELECT * FROM `users` WHERE `age` = 18 OR `age` = 21
You can group your conditions in order to add a more complex filter to your query.
Grouping conditions is done by passing as an argument to the where, andWhere or the orWhere
methods an anonymous callback function. The callback functions takes a single argument
that will be further used to add filtering conditions to your query.
/**
* Select all users that are 18 and
* are living either in London or Paris.
*/
$result = $db->from('users')
->where('age')->is(18)
->andWhere(function($group){
$group->where('city')->is('London')
->orWhere('city')->is('Paris');
})
->select()
->all();
SELECT * FROM `users` WHERE `age` = 18 AND (`city` = "London" OR `city` = "Paris")
Columns comparison
Opis Database allows you to add comparison conditions between columns.
You can add comparison conditions between two columns, by passing TRUE as a second argument
to the is, isNot, lessThan, greaterThan, atLeast and atMost methods, or to their
corresponding aliases eq , ne, lt, gt, lte and gte methods.
/**
* Select all users that are living in the same city they were born.
*/
$result = $db->from('users')
->where('city')->eq('birthplace', true)
->select()
->all();
SELECT * FROM `users` WHERE `city` = `birthplace`
Building the above query without passing TRUE as the second argument to the eq method,
will result into a query that will select all users that are living in a city named birthplace.
$result = $db->from('users')
->where('city')->eq('birthplace')
->select()
->all();
SELECT * FROM `users` WHERE `city` = "birthplace"
The EXISTS condition
This condition is used in combination with a subquery and is considered to be met,
if the subquery returns at least one row. Adding an EXISTS condition is done by using the
whereExists and the whereNotExists methods. You can add multiple EXISTS conditions by using
the andWhereExists or andWhereNotExists methods and the orWhereExists or orWhereNotExists methods.
These methods are used in a similar manner as the where, andWhere and orWhere methods,
receiving as an argument an anonymous function callback, that will be further used to build a subquery.
/**
* Select all users that had purchased at least one product.
*/
$result = $db->from('users')
->whereExists(function($query){
$query->from('orders')
->where('orders.name')->eq('users.name', true)
->select();
})
->select()
->all();
SELECT * FROM `users`
WHERE EXISTS (SELECT * FROM `orders` WHERE `orders`.`name` = `users`.`name`)
