Creating queries dynamically using objects and methods allows queries to be written very quickly in an agnostic way. Query building also adds identifier (table and column name) quoting, as well as value quoting.

Select

Each type of database query is represented by a different class, each with their own methods. For instance, to create a SELECT query, we use DB::select which is a shortcut to return a new Database_Query_Builder_Select object:

$query = DB::select();

Query Builder methods return a reference to itself so that method chaining may be used. Select queries ussually require a table and they are referenced using the from() method. The from() method takes one parameter which can be the table name (string), an array of two strings (table name and alias), or an object (See Subqueries in the Advanced Queries section below).

$query = DB::select()->from('users');

Limiting the results of queries is done using the where(), and_where() and or_where() methods. These methods take three parameters: a column, an operator, and a value.

$query = DB::select()->from('users')->where('username', '=', 'john');

Multiple where() methods may be used to string together multiple clauses connected by the boolean operator in the method's prefix. The where() method is a wrapper that just calls and_where().

Now take a minute to look at what this method chain is doing. First, we create a new selection object using the DB::select method. Next, we set table(s) using the from() method. Last, we search for a specific records using the where() method. We can display the SQL that will be executed by casting the query to a string:

Select - DISTINCT

Unique column values may be turned on or off (default) by passing TRUE or FALSE, respectively, to the distinct() method.

$query = DB::select('username')->distinct(TRUE)->from('posts');

This query would generate the following SQL:

SELECT DISTINCT `username` FROM `posts`

Select - LIMIT & OFFSET

When querying large sets of data, it is often better to limit the results and page through the data one chunk at a time. This is done using the limit() and offset() methods.

$query = DB::select()->from(`posts`)->limit(10)->offset(30);

This query would generate the following SQL:

SELECT * FROM `posts` LIMIT 10 OFFSET 30

Select - ORDER BY

Often you will want the results in a particular order and rather than sorting the results, it's better to have the results returned to you in the correct order. You can do this by using the order_by() method. It takes the column name and an optional direction string as the parameters. Multiple order_by() methods can be used to add additional sorting capability.

Advanced Queries

Joins

Multiple tables can be joined using the join() and on() methods. The join() method takes two parameters. The first is either a table name, an array containing the table and alias, or an object (subquery or expression). The second parameter is the join type: LEFT, RIGHT, INNER, etc.

The on() method sets the conditions for the previous join() method and is very similar to the where() method in that it takes three parameters; left column (name or object), an operator, and the right column (name or object). Multiple on() methods may be used to supply multiple conditions and they will be appended with an 'AND' operator.

When joining multiple tables with similar column names, it's best to prefix the columns with the table name or table alias to avoid errors. Ambiguous column names should also be aliased so that they can be referenced easier.

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:

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.

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.

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.