Database - Queries - Query Builder 4

Database Functions

Eventually you will probably run into a situation where you need to call COUNT or some other database function within your query. The query builder supports these functions in two ways. The first is by using quotes within aliases:

?

$query = DB::select(array('COUNT("username")', 'total_users'))->from('users');

This looks almost exactly the same as a standard AS alias, but note how the column name is wrapped in double quotes. Any time a double-quoted value appears inside of a column name, only the part inside the double quotes will be escaped. This query would generate the following SQL:

?

SELECT COUNT(`username`) AS `total_users` FROM `users`

When building complex queries and you need to get a count of the total rows that will be returned, build the expression with an empty column list first. Then clone the query and add the COUNT function to one copy and the columns list to the other. This will cut down on the total lines of code and make updating the query easier.

?

$query = DB::select()->from('users')
    ->join('posts')->on('posts.username', '=', 'users.username')
    ->where('users.active', '=', TRUE)
    ->where('posts.created', '>=', $yesterday);
 
$total = clone $query;
$total->select(array('COUNT( DISTINCT "username")', 'unique_users'));
$query->select('posts.username')->distinct();

Aggregate Functions

Aggregate functions like COUNT(), SUM(), AVG(), etc. will most likely be used with the group_by() and possibly the having()methods in order to group and filter the results on a set of columns.

?

$query = DB::select('username', array('COUNT("id")', 'total_posts')
    ->from('posts')->group_by('username')->having('total_posts', '>=', 10);

This will generate the following query:

?

SELECT `username`, COUNT(`id`) AS `total_posts` FROM `posts` GROUP BY `username` HAVING `total_posts` >= 10

Subqueries

Query Builder objects can be passed as parameters to many of the methods to create subqueries. Let's take the previous example query and pass it to a new query.

?

$sub = DB::select('username', array('COUNT("id")', 'total_posts')
    ->from('posts')->group_by('username')->having('total_posts', '>=', 10);
 
$query = DB::select('profiles.*', 'posts.total_posts')->from('profiles')
    ->join(array($sub, 'posts'), 'INNER')->on('profiles.username', '=', 'posts.username');

This will generate the following query:

?

SELECT `profiles`.*, `posts`.`total_posts` FROM `profiles` INNER JOIN
( SELECT `username`, COUNT(`id`) AS `total_posts` FROM `posts` GROUP BY `username` HAVING `total_posts` >= 10 ) AS posts
ON `profiles`.`username` = `posts`.`username`

Insert queries can also use a select query for the input values

?

$sub = DB::select('username', array('COUNT("id")', 'total_posts')
    ->from('posts')->group_by('username')->having('total_posts', '>=', 10);
 
$query = DB::insert('post_totals', array('username', 'posts'))->select($sub);

This will generate the following query:

?

INSERT INTO `post_totals` (`username`, `posts`) 
SELECT `username`, COUNT(`id`) AS `total_posts` FROM `posts` GROUP BY `username` HAVING `total_posts` >= 10 

Boolean Operators and Nested Clauses

Multiple Where and Having clauses are added to the query with Boolean operators connecting each expression. The default operator for both methods is AND which is the same as the and_ prefixed method. The OR operator can be specified by prefixing the methods with or_. Where and Having clauses can be nested or grouped by post fixing either method with _open and then followed by a method with a _close.

?

$query = DB::select()->from('users')
    ->where_open()
        ->or_where('id', 'IN', $expired)
        ->and_where_open()
            ->where('last_login', '<=', $last_month)
            ->or_where('last_login', 'IS', NULL)
        ->and_where_close()
    ->where_close()
    ->and_where('removed','IS', NULL);

This will generate the following query:

?

SELECT * FROM `users` WHERE ( `id` IN (1, 2, 3, 5) OR ( `last_login` <= 1276020805 OR `last_login` IS NULL ) ) AND `removed` IS NULL 

Database Expressions

There are cases were you need a complex expression or other database functions, which you don't want the Query Builder to try and escape. In these cases, you will need to use a database expression created with DB::expr. A database expression is taken as direct input and no escaping is performed.

?

$query = DB::update('users')->set(array('login_count', DB::expr('login_count + 1')))->where('id', '=', $id);

This will generate the following query, assuming $id = 45:

?

UPDATE `users` SET `login_count` = `login_count` + 1 WHERE `id` = 45

Another example to calculate the distance of two geographical points:

?

$query = DB::select(array(DB::expr('degrees(acos(sin(radians('.$lat.')) * sin(radians(`latitude`)) + cos(radians('.$lat.')) * cos(radians(`latitude`)) * cos(radians(abs('.$lng.' - `longitude`))))) * 69.172'), 'distance'))->from('locations');

You must validate or escape any user input inside of DB::expr as it will obviously not be escaped it for you.

Executing

Once you are done building, you can execute the query using execute() and use the results.

?

$result = $query->execute();

To use a different database config group pass either the name or the config object to execute().

?

$result = $query->execute('config_name')

---