Query builder is a tool for constructing SQL query. It allows you to define SQL query with fluent API.
The API is designed to be convenient and yet safe and helpful. Therefore every method accepts specific arguments and if any of them is suffixed “expression”, the method also allow to pass a modifier it this argument. The last argument(s) of every method is then these values for the used modifiers in the expressions.
To get query in QueryBuilder executed, pass the builder to
queryByQueryBuilder() method on connection, you will receive a
$builder = $connection->createQueryBuilder(); // modify query builder $result = $this->connection->queryByQueryBuilder($builder);
Let's create a Query builder instance. Set a source table name by
from() method. You can pass an alias. The first argument is
considered as a SQL expression, you should escape its table name manually with
 brackets, or use a modifier. The second argument is an optional
table alias, other arguments are arguments for used modifiers.
// table useres, aliased a $builder->from('users', 'a'); // escaping table name $builder->from('[orders]', 'o'); // or pass it as argument $builder->from('%table', 'o', $tableName); // table as result of stored function/procedure $builder->from('my_orders(%i, %i)', 'orders', $userId, $groupId);
You can add conditions using
*Where methods accept query
expression, all other function arguments are optional and are considered as
arguments for modifiers passed in the expression.
$builder->andWhere('order_id = %i', $oderId); ->orWhere('user_id IN %i AND group_id = %i', $userIds, $groupId); // will produce // WHERE (order_id = %i) OR (user_id IN %i AND group_id = %i)
orHaving() have the same
signature and logic as where methods.
Group by expressions are not connected with logic operators, therefore the
method is called
addGroupBy(). The group by method has signature as
others, accepts an expression and optinal arguments.
Let's see an example: depending on
$cond we build query which
will retrieve daily number of issues created/resolved in the last week and
filter only days with more than 10 issues.
$column = $cond ? 'created_at' : 'resolved_at'; $builder = $connection->createQueryBuilder(); $builder->select('DATE(%column), COUNT(*)', $column); $builder->from('issues'); $builder->andWhere('%column > NOW() - INTERVAL 1 WEEK', $column); $builder->addGroupBy('DATE(%column)', $column); $builder->andHaving('COUNT(*) > 10');
Query builder has alternative methods named
having() which remove previously defined
conditions and add new one:
$builder = $connection->createQueryBuilder(); $builder->from('issues'); $builder->where('created_at > NOW()'); $builder->where('created_at < NOW()'); // replace previous conditions // will produce // SELECT * FROM issues WHERE created_at < NOW();
You can also use these methods to empty the clause.
Builder define methods for select, order by and limit clauses. Use
Select and order by methods accept modifier arguments as aformentioned
$builder->addSelect('id, %column, [another_ecpaced_column]', $myColumn); $builder->addSelect('COALESCE(colmun)'); $builder->addOrderBy('FIELD(type, %s, %s, %s)', "type1", "type2", "type3"); $builder->limitBy(20); // selects the first 20 results $builder->limitBy(20, 10); // sets offset to 1O
rightJoin() methods. Each of them has the same signature.
The “from source name” is present for better validation. It disallows you to build join on table which has not been defined yet in the QueryBuilder.
$builder->from('[authors]', 'a'); $builder->leftJoin('a', '[books]', 'b', '[a.id] = [b.authorId] AND [b.title] = %s', $title); // will produce // FROM [authors] AS [a] // LEFT JOIN [books] AS [b] ON ([a.id] = [b.authorId] AND [b.title] = %s)