Building SQL statements

Leap makes building SQL statements easy using its query builder. Leap’s query builder is capable of building create, read, update, and delete (CRUD) statements. The query builder will help prevent SQL insertion attacks by escaping identifiers and values. The query builder also makes it easier to switch database dialects for it standardizes the way you write SQL statements.

Depending on what your needs are, you can use either ‘DB_SQL’ or ‘DB_ORM’ to generate your SQL statements. These two classes are for the most part the same; however, there are some minor differences between them. The main difference between the classes is that one uses a data source to determine the database dialect and the another uses a model to determine the dialect. For some commands, DB_ORM is a little more restrictive than DB_SQL; however, DB_ORM can be extended using a model’s corresponding builder class. And, with select statements, DB_ORM will return an array of models of the specified type whereas DB_SQL will return an array of associated arrays. Notwithstanding these minor differences, they both essentially act the same and share the majority of the same functions.

Create (aka Insert) Statements

Inserting a single record

The query builder simplifies the writing of insert statements. The following example demonstrates how to build an insert statement using DB_SQL:

 1 <?php
 2 $builder = DB_SQL::insert('default')
 3    ->into('user')
 4    ->column('Username', 'spadefoot')
 5    ->column('Password', sha1('a5b4c3d2e1'))
 6    ->column('FirstName', 'John')
 7    ->column('LastName', 'Smith')
 8    ->column('IsActive', TRUE);
 9 $sql = $builder->statement();
10 $id = $builder->execute();

The same can be accomplished using DB_ORM; however, notice that there is no call to the ‘into’ function and that the database config id has been replaced with the name of the model:

1 <?php
2 $builder = DB_ORM::insert('user')
3    ->column('Username', 'spadefoot')
4    ->column('Password', sha1('a5b4c3d2e1'))
5    ->column('FirstName', 'John')
6    ->column('LastName', 'Smith')
7    ->column('IsActive', TRUE);
8 $sql = $builder->statement();
9 $id = $builder->execute();

In the above examples, please note that you do not need to call statement() to execute the SQL statement. It is just in case you want to debug the SQL statement; otherwise, you can just chain execute() onto your other calls.

Inserting multiple records

There are two ways you can insert multiple records into a database in one SQL statement (i.e. as long as your database dialect supports such types of insertions; otherwise, only the first row will be inserted). However, this feature is only supported by DB_SQL.

The first way is the most intuitive, which uses the “row” method. Notice the second parameter which represents the index of the row.

1 <?php
2 DB_SQL::insert('default')
3    ->into('user')
4    ->row(array('Username' => 'spadefoot', 'Password' => sha1('a5b4c3d2e1'), 0)
5    ->row(array('Username' => 'bluesnowman', 'Password' => sha1('z1y2x3w4v5'), 1)
6    ->execute();

The other way to insert multiple records is to use the “column” method. Like in the previous example, notice the third parameter which represents the index of the row.

1 <?php
2 DB_SQL::insert('default')
3    ->into('user')
4    ->column('Username', 'spadefoot', 0)
5    ->column('Password', sha1('a5b4c3d2e1'), 0)
6    ->column('Username', 'bluesnowman', 1)
7    ->column('Password', sha1('z1y2x3w4v5'), 1)
8    ->execute();

Read (aka Select) Statements

You can build a simple select statement by doing the following using DB_SQL:

1 <?php
2 $builder = DB_SQL::select('default')
3    ->from('user');
4 $sql = $builder->statement();

The same can be accomplished with the DB_ORM:

1 <?php
2 $builder = DB_ORM::select('user');
3 $sql = $builder->statement();

Either builder can query a database using the query method:

1 <?php
2 $results = $builder->query();

Or, you can use the reader method with DB_SQL:

1 <?php
2 $reader = $builder->reader();

Both builders can be further modified using the following functions:

from

With DB_SQL, you can assign an alias to table’s name.

1 <?php
2 $builder->from('user', 'u');

However, DB_ORM does not support aliases for table names.

column

By default, DB_SQL will select all records from the specified table. To just select certain columns, you can do the following:

1 <?php
2 $builder->column('FirstName');

You can specify an alias for this column by:

1 <?php
2 $builder->column('FirstName', 'GivenName');

However, DB_ORM does not support aliases for column names.

count

There is also a short-cut way of creating a count expression using DB_SQL’s select builder, which will create an expression like so: COUNT(*) AS “count”.

1 <?php
2 $builder->count();

You can alter the field the count function is performed by doing the following (which is create an expression like COUNT(“id”) AS “count”):

1 <?php
2 $builder->count('id');

Like other columns, you can assign an alias:

1 <?php
2 $builder->count('*', 'total');

distinct

Some queries may require that the result set on contain records that are distinct. This can be done by setting the distinct function to TRUE.

1 <?php
2 $builder->distinct(TRUE);

This may done via both DB_SQL and DB_ORM.

join

Both DB_SQL and DB_ORM can join tables; however, DB_ORM cannot return data from the joined tables (i.e. since DB_ORM returns a result set of models of the specified type and a model essentially represents a row in a database table, an instance of a model class will not store any data from the joined tables…for that you will need to use DB_SQL).

The join function is written like so:

1 <?php
2 $builder->join('LEFT', 'role');

If you want to assign an alias to the joined table, add the following third parameter:

1 <?php
2 $builder->join('LEFT', 'role', 'r');

Although you can use strings for specifying the join type, you can also use one of LEAP’ predefined join type constants. Considering that different SQL dialects use different join types, here is a simple lookup table for determining whether your SQL dialect supports a particular join type:

on

To place a constraint on a join, it is done like so in both DB_SQL and DB_ORM:

1 <?php
2 $builder->on('Roles.User_ID', '=', 'User.ID');

using

Both DB_SQL and DB_ORM also support the using constrain:

1 <?php
2 $builder->using('Username');

where

Adding a where clause can be done in DB_SQL and DB_ORM.

1 <?php
2 $builder->where('LastName', '=', 'Smith');

Multiple where clauses can be affixed. By default, when there are more than one where clause, the builder will connect such where clauses using the AND connector; however, you can change the connector to use the OR connector like so:

1 <?php
2 $builder->where('FirstName', '=', 'John', 'OR');

As expected, LEAP supports all comparison operators that SQL supports. In most cases, you can just replace the ’=’ sign with the comparison operator of your choice. See your specific SQL dialect’s API for more details on what comparison operators your SQL dialect supports.

However, there are two types of comparison operators that all SQL dialects have that are worth noting: the BETWEEN and IN comparison operators. With both of these operators, the third parameter in the where call is an array.

Here is an example using the BETWEEN operator:

1 <?php
2 $builder->where('DateCreated', 'BETWEEN', array('2011-01-01 00:00:00', '2012-12-31 23:59:59'));

Below is an example using the IN operator:

1 <?php
2 $builder->where('FirstName', 'IN', array('Matthew', 'Mark', 'Luke', 'John'));

where_block

In some circumstances, it may be necessary to group a set of where clauses together. To do so, LEAP provides a function for specifying such a group. To open a where block, do the following:

1 <?php
2 $builder->where_block('(');

To close this where block, you just use the closing parenthesis as in the following example:

1 <?php
2 $builder->where_block(')');

Like the where call, you can change the connector used (which by default is the AND connector):

1 <?php
2 $builder->where_block('(', 'OR');

group_by

Specifying a group_by clause is simple:

1 <?php
2 $builder->group_by('FirstName');

Although you can add as many group_by clauses as you want using multiple function calls, you can specify more than one field in a single function call…such as in the following example:

1 <?php
2 $builder->group_by(array('LastName', 'FirstName'));

having

Adding a having clause is identical to adding a where clause, except it must be declared only after at least one group by clause has been declared; otherwise, an exception will be thrown.

1 <?php
2 $builder->having('LastName', '=', 'Smith');

If you need more than one having clauses and need to change the connector, it can be done in the same fashion as the where clause:

1 <?php
2 $builder->having('FirstName', '=', 'John', 'OR');

If you need to use a comparison operator like BETWEEN and IN, do the same as in the where clause example above.

having_block

Likewise, a having block is defined the same way as a where block. A having block is created like so:

1 <?php
2 $builder->having_block('(');

And, a having block is closed like so:

1 <?php
2 $builder->having_block(')');

The connector can be changed in the same manner:

1 <?php
2 $builder->having_block('(', 'OR');

order_by

An order by clause can be declared by:

1 <?php
2 $builder->order_by('LastName');

If you want to specify the sort direction, do the following:

1 <?php
2 $builder->order_by('LastName', 'DESC');

You can take it a little farther by defining how NULLs are to be treated:

1 <?php
2 $builder->order_by('LastName', 'DESC', 'LAST');

limit

A limit clause is created like so:

1 <?php
2 $builder->limit(5);

offset

An offset clause is created like so:

1 <?php
2 $builder->offset(20);

page

If you prefer, you can add both the offset and limit constraints using the page function, where the first parameter is the offset and the second parameter is the limit:

1 <?php
2 $builder->page(20, 5);

combine

In cases where you need to combine two SQL statements, you can do so using the following function call:

1 <?php
2 $builder->combine('UNION', "SELECT * FROM `employee`");

Update Statements

In its simplest form, an update statement is created using the DB_SQL like so:

1 <?php
2 $builder = DB_SQL::update('default')
3    ->table('user')
4    ->set('Username', 'spadefoot')
5    ->where('ID', '=', 15);
6 $sql = $builder->statement();
7 $id = $builder->execute();

The DB_ORM also creates update statements, for example:

1 <?php
2 $builder = DB_ORM::update('user')
3    ->set('Username', 'spadefoot')
4    ->where('ID', '=', 15);
5 $sql = $builder->statement();
6 $id = $builder->execute();

where

As you can see above, both DB_SQL and DB_ORM are able to add where clauses. The syntax for adding a where clause is:

1 <?php
2 $builder->where('LastName', '=', 'Smith');

This function has a fourth parameter, which can be used to change the connector that will be used when using multiple where clauses:

1 <?php
2 $builder->where('FirstName', '=', 'John', 'OR');

To create a where clause using the BETWEEN operator, the syntax will be as follows:

1 <?php
2 $builder->where('DateCreated', 'BETWEEN', array('2011-01-01 00:00:00', '2012-12-31 23:59:59'));

Similarly, a where clause using the IN operator is create like so:

1 <?php
2 $builder->where('FirstName', 'IN', array('Matthew', 'Mark', 'Luke', 'John'));

where_block

Like the select builder, you can create where blocks.

1 <?php
2 $builder->where_block('(');

It is closed by:

1 <?php
2 $builder->where_block(')');

For when you need to change the connector, do:

1 <?php
2 $builder->where_block('(', 'OR');

order_by

If your update statement requires an order by clause, you can add it by:

1 <?php
2 $builder->order_by('LastName');

You can change the sort direction by:

1 <?php
2 $builder->order_by('LastName', 'DESC');

An additional parameter can be set to assign how NULLs should be treated:

1 <?php
2 $builder->order_by('LastName', 'DESC', 'LAST');

limit

You can add a limit clause by:

1 <?php
2 $builder->limit(5);

offset

An offset clause can also be added to your update statement by:

1 <?php
2 $builder->offset(20);

Delete Statements

You can build a delete statement using DB_SQL like so:

1 <?php
2 $builder = DB_SQL::delete('default')
3    ->from('user')
4    ->where('ID', 15);
5 $sql = $builder->statement();
6 $id = $builder->execute();

You can also build a delete statement with DB_ORM as the following example shows:

1 <?php
2 $builder = DB_ORM::delete('user')
3    ->where('ID', '=', 15);
4 $sql = $builder->statement();
5 $id = $builder->execute();

where

To add a where clause to your delete statement, use the following call:

1 <?php
2 $builder->where('LastName', '=', 'Smith');

If you want, you can pass the connector you desire like this example shows:

1 <?php
2 $builder->where('FirstName', '=', 'John', 'OR');

A BETWEEN operator is created by:

1 <?php
2 $builder->where('DateCreated', 'BETWEEN', array('2011-01-01 00:00:00', '2012-12-31 23:59:59'));

An IN operator is created in the same way:

1 <?php
2 $builder->where('FirstName', 'IN', array('Matthew', 'Mark', 'Luke', 'John'));

where_block

A where block can be added with the following call:

1 <?php
2 $builder->where_block('(');

A where block can be closed by:

1 <?php
2 $builder->where_block(')');

To change the connector, use the following call:

1 <?php
2 $builder->where_block('(', 'OR');

order_by

If you choose to add an order by clause to your delete statement, do so in the following way:

1 <?php
2 $builder->order_by('LastName');

You may specify the sort direction explicitly if you want like so:

1 <?php
2 $builder->order_by('LastName', 'DESC');

If situations where you need to specify how NULLs will be treated, use the following call:

1 <?php
2 $builder->order_by('LastName', 'DESC', 'LAST');

limit

You can also limit a delete statement.

1 <?php
2 $builder->limit(5);

offset

An offset may also be specified by using a call similar to this one:

1 <?php
2 $builder->offset(20);