SQL Query Builder

DISTINCT-Clause

The SELECT statement can be specified with a DISTINCT clause:

1
<?php

$queryBuilder
    ->select('name')
    ->distinct()
    ->from('users')
;
2
3
4
5
6
7

WHERE-Clause

The SELECT, UPDATE and DELETE types of queries allow where clauses with the following API:

1
<?php

$queryBuilder
    ->select('id', 'name')
    ->from('users')
    ->where('email = ?')
;
2
3
4
5
6
7

Calling where() overwrites the previous clause and you can prevent this by combining expressions with andWhere() and orWhere() methods. You can alternatively use expressions to generate the where clause.

Table alias

The from() method takes an optional second parameter with which a table alias can be specified.

1
<?php

$queryBuilder
    ->select('u.id', 'u.name')
    ->from('users', 'u')
    ->where('u.email = ?')
;
2
3
4
5
6
7

GROUP BY and HAVING Clause

The SELECT statement can be specified with GROUP BY and HAVING clauses. Using having() works exactly like using where() and there are corresponding andHaving() and orHaving() methods to combine predicates. For the GROUP BY you can use the methods groupBy() which replaces previous expressions or addGroupBy() which adds to them:

1
<?php
$queryBuilder
    ->select('DATE(last_login) as date', 'COUNT(id) AS users')
    ->from('users')
    ->groupBy('DATE(last_login)')
    ->having('users > 10')
;
2
3
4
5
6
7

Join Clauses

For SELECT clauses you can generate different types of joins: INNER, LEFT and RIGHT. The RIGHT join is not portable across all platforms (Sqlite for example does not support it).

A join always belongs to one part of the from clause. This is why you have to specify the alias of the FROM part the join belongs to as the first argument.

As a second and third argument you can then specify the name and alias of the join-table and the fourth argument contains the ON clause.

1
<?php
$queryBuilder
    ->select('u.id', 'u.name', 'p.number')
    ->from('users', 'u')
    ->innerJoin('u', 'phonenumbers', 'p', 'u.id = p.user_id')
2
3
4
5

The method signature for join(), innerJoin(), leftJoin() and rightJoin() is the same. join() is a shorthand syntax for innerJoin().

Order-By Clause

The orderBy($sort, $order = null) method adds an expression to the `ORDER BY`` clause. Be aware that the optional ``$order` parameter is not safe for user input and accepts SQL expressions.

1
<?php
$queryBuilder
    ->select('id', 'name')
    ->from('users')
    ->orderBy('username', 'ASC')
    ->addOrderBy('last_login', 'ASC NULLS FIRST')
;
2
3
4
5
6
7

Use the addOrderBy method to add instead of replace the orderBy clause.

Limit Clause

Only a few database vendors have the LIMIT clause as known from MySQL, but we support this functionality for all vendors using workarounds. To use this functionality you have to call the methods setFirstResult($offset) to set the offset and setMaxResults($limit) to set the limit of results returned.

1
<?php
$queryBuilder
    ->select('id', 'name')
    ->from('users')
    ->setFirstResult(10)
    ->setMaxResults(20);
2
3
4
5
6

VALUES Clause

For the INSERT clause setting the values for columns to insert can be done with the values() method on the query builder:

1
<?php

$queryBuilder
    ->insert('users')
    ->values(
        [
            'name' => '?',
            'password' => '?',
        ]
    )
    ->setParameter(0, $username)
    ->setParameter(1, $password)
;
// INSERT INTO users (name, password) VALUES (?, ?)
2
3
4
5
6
7
8
9
10
11
12
13
14

Each subsequent call to values() overwrites any previous set values. Setting single values instead of all at once is also possible with the setValue() method:

1
<?php

$queryBuilder
    ->insert('users')
    ->setValue('name', '?')
    ->setValue('password', '?')
    ->setParameter(0, $username)
    ->setParameter(1, $password)
;
// INSERT INTO users (name, password) VALUES (?, ?)
2
3
4
5
6
7
8
9
10

Of course you can also use both methods in combination:

1
<?php

$queryBuilder
    ->insert('users')
    ->values(
        [
            'name' => '?',
        ]
    )
    ->setParameter(0, $username)
;
// INSERT INTO users (name) VALUES (?)

if ($password) {
    $queryBuilder
        ->setValue('password', '?')
        ->setParameter(1, $password)
    ;
    // INSERT INTO users (name, password) VALUES (?, ?)
}
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Not setting any values at all will result in an empty insert statement:

1
<?php

$queryBuilder
    ->insert('users')
;
// INSERT INTO users () VALUES ()
2
3
4
5
6

Set Clause

For the UPDATE clause setting columns to new values is necessary and can be done with the set() method on the query builder. Be aware that the second argument allows expressions and is not safe for user-input:

1
<?php

$queryBuilder
    ->update('users', 'u')
    ->set('u.logins', 'u.logins + 1')
    ->set('u.last_login', '?')
    ->setParameter(0, $userInputLastLogin)
;
2
3
4
5
6
7
8