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:
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.
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.
SELECT * FROM `users` WHERE ( `id` IN (1, 2, 3, 5) OR ( `last_login` <= 1276020805 OR `last_login` IS NULL ) ) AND `removed` IS NULL
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.