Query Builders

Table of Contents

  1. Introduction
  2. Basic Usage
  3. Clauses
    1. Conditions
  4. Binding Values
  5. Select Queries
  6. Insert Queries
    1. Using Expressions in Insert Queries
  7. Update Queries
    1. Using Expressions in Update Queries
  8. Delete Queries
  9. Using Query Builders with PDO
  10. Vendor-Specific Query Builders

Introduction

Sometimes you need to programmatically generate SQL queries. Rather than concatenating strings together, you can use QueryBuilders to do the heavy lifting. They provide a fluent syntax for creating queries and binding values to queries. This library is adapter-agnostic, meaning you can use it with any database adapter or with any library, such as PHP's PDOStatement or Opulence's PDO wrapper. Query builders even support vendor-specific query features, such as MySQL's LIMIT clause support for DELETE statements.

Basic Usage

Let's look at a simple SELECT query:

use Opulence\QueryBuilders\PostgreSql\QueryBuilder;

$query = (new QueryBuilder)->select('id', 'name', 'email')
    ->from('users')
    ->where('datejoined < NOW()');
echo $query->getSql();

This will output:

SELECT id, name, email FROM users WHERE datejoined < NOW()

Clauses

QueryBuilders support a variety of clauses. You may use the following clauses to build complex, but easy-to-read-and-maintain queries:

Conditions

Opulence provides an easy way to add conditions to your queries using Opulence\QueryBuilders\Conditions\ConditionFactory. It provides methods for creating the following conditions in your where() and having() clauses:

You can also negate these methods:

Here's an example of how to grab all users whose Id matches at least one of the input values:

use Opulence\QueryBuilders\Conditions\ConditionFactory;

$conditions = new ConditionFactory();
$query = (new QueryBuilder)->select('name')
    ->from('users')
    ->where($conditions->in('id', [[23, \PDO::PARAM_INT], [33, \PDO::PARAM_INT]]));

Using ConditionFactory will automatically bind any values as unnamed placeholders.

Note: If you're trying to include complex expressions in your conditions, eg birthday BETWEEN NOW() AND CAST('2050-01-01' AS Date), you're best off just writing them as strings in the where() or having() clauses.

Binding Values

QueryBuilders provide an intuitive syntax for binding values to queries (learn more about statement bindings). To add a named placeholder, use addNamedPlaceholderValue():

$query = (new QueryBuilder)->select('content')
    ->from('posts')
    ->where('id < :id')
    ->addNamedPlaceholderValue('id', 24, \PDO::PARAM_INT);

To add many named placeholder values, use addNamedPlaceholderValues():

$query = (new QueryBuilder)->select('count(*)')
    ->from('users')
    ->where('username = :username')
    ->orWhere('id = :id')
    ->addNamedPlaceholderValues([
        // Non-array values are assumed to be of type \PDO::PARAM_STR
        'username' => 'dave_y',
        // In array values, the first item is the value, and the second is the parameter type
        'id' => [24, \PDO::PARAM_INT]
    ]);

Similarly, addUnnamedPlaceholderValue() and addUnnamedPlaceholderValues() can be used to add unnamed placeholder values.

Note: You cannot mix named with unnamed placeholders. Also, if no type is specified for a bound value, it's assumed to be \PDO::PARAM_STR.

Select Queries

Select queries use a variable argument list to specify the columns to select:

$query = (new QueryBuilder)->select('title', 'author')
    ->from('books');
echo $query->getSql();

This will output:

SELECT title, author FROM books

Insert Queries

Insert queries accept a table name and a mapping of column names to values:

$query = (new QueryBuilder)->insert('users', [
    'name' => 'Brian',
    'email' => 'foo@bar.com',
    'age' => [24, \PDO::PARAM_INT]
]);
echo $query->getSql();

This will output:

INSERT INTO users (name, email, age) VALUES (?, ?, ?)

The following values are bound to the query:

[
    ['Brian', \PDO::PARAM_STR],
    ['foo@bar.com', \PDO::PARAM_STR],
    [24, \PDO::PARAM_INT]
]

Note: INSERT and UPDATE query builders bind unnamed placeholder values. To specify the type of the value, use an array whose first item is the value and whose second item is the type.

Using Expressions In Insert Queries

If you need to set some values as expressions, you can use Expression objects in the column names to values array:

use Opulence\QueryBuilders\Expression;
use Opulence\QueryBuilders\QueryBuilder;

$query = (new QueryBuilder)->insert('users', [
    'name' => 'Brian',
    'email' => 'foo@bar.com',
    'uniq' => new Expression('SHA1(CONCAT(name, email, ?))', ['unique_salt', \PDO::PARAM_STR]),
]);
echo $query->getSql();

This will output:

INSERT INTO users (name, email, uniq) VALUES (?, ?, SHA1(CONCAT(name, email, ?)))

The following values are bound to the query:

[
    ['Brian', \PDO::PARAM_STR],
    ['foo@bar.com', \PDO::PARAM_STR],
    ['unique_salt', \PDO::PARAM_STR]
]

Update Queries

Update queries accept a table name, table alias, and a mapping of column names to values:

$query = (new QueryBuilder)->update('users', 'u', [
    'name' => 'Dave',
    'age' => [24, \PDO::PARAM_INT]
    ])
    ->where('id = ?')
    ->addUnnamedPlaceholderValue(1234, \PDO::PARAM_INT);
echo $query->getSql();

This will output:

UPDATE users AS u SET name = ?, age = ? WHERE id = ?

The following values are bound to the query:

[
    ['Dave', \PDO::PARAM_STR],
    [24, \PDO::PARAM_INT],
    [1234, \PDO::PARAM_INT]
]

Note: Like INSERT query builders, UPDATE query builders bind unnamed placeholder values.

Using Expressions in Update Queries

You can use SQL expressions in UPDATE queries just like you can with INSERT queries.

Delete Queries

Delete queries accept a table name:

$query = (new QueryBuilder)->delete('users')
    ->where('id = :id');
echo $query->getSql();

This will output:

DELETE FROM users WHERE id = :id

Using Query Builders with PDO

Let's say you've built the following query:

$query = (new QueryBuilder)->select('author')
    ->from('books')
    ->where('title = :title')
    ->addNamedPlaceholderValue('title', 'Code Complete');

Simply call getSql() and getParameters() to use this in PDO or in Opulence's PDO wrapper:

$statement = $connection->prepare($query->getSql());
$statement->bindValues($query->getParameters());
$statement->execute();

Vendor-Specific Query Builders

MySQL and PostgreSQL have their own query builders, which implement features that are unique to each database. For example, the MySQL query builder supports a LIMIT clause:

use Opulence\QueryBuilders\MySql\QueryBuilder;

$query = (new QueryBuilder)->delete('users')
    ->where("name = 'Dave'")
    ->limit(1);
echo $query->getSql();

This will output:

DELETE FROM users WHERE name = 'Dave' LIMIT 1

Similarly, PostgreSQL's UPDATE and INSERT query builders support a RETURNING clause:

use Opulence\QueryBuilders\PostgreSql\QueryBuilder;

$query = (new QueryBuilder)->update('users', '', [
    'status' => [0, \PDO::PARAM_INT]
    ])
    ->returning('id')
    ->addReturning('name');
echo $query->getSql();

This will output:

UPDATE users SET status = ? RETURNING id, name

The following values are bound to the query:

[
    [0, \PDO::PARAM_INT]
]

Here's an example of an INSERT statement with a RETURNING clause:

use Opulence\QueryBuilders\PostgreSql\QueryBuilder;

$query = (new QueryBuilder)->insert('users', '', ['name' => 'David'])
    ->returning('id')
    ->addReturning('name');
echo $query->getSql();

This will output:

INSERT INTO users (name) VALUES (?) RETURNING id, name

The following values are bound to the query:

[
    ['David', \PDO::PARAM_STR]
]