This document describes how to generate and execute SQL queries using a query builder (mostly the builder was modelled off Laravel Query Builder, which already provides quite a thorough documentation - however some methods might be a bit different). Any significant difference will be outlined in this document as a separate paragraph.
...
Other functions
value()
Returns the a single value from a record. This method will return the value of the column directly.
Section | ||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
...
To select items from your database you should use select or add_select method, the first one overrides your SQL select statement, the latter one appends. Field names are passed through a standard regex check to help you against SQL injections.
Will give you the following result:
...
id
...
name
...
1
...
John Doe
Selecting aggregates
Sometimes you need to do more complex selects than just a field name, for example using aggregate functions, builder allows for it, treating it as valid column names. Most commonly aggregate functions are supported: MIN, MAX, AVG, SUM, COUNT.
Will give you the following result
...
Selecting subquery
It's possible to use subqueries in select statements as well
The above query should give you the following results:
...
If this is still not sufficient, then you might resort to the raw versions of the select methods:
Selecting raw statement
When you use select statement the field that you are attempting to select is validated against a regular expression "a-zA-Z-_|*" (it actually is a bit more complex to allow for aliases, aggregate functions and prefixes - more on that later). Sometimes though you might need to use more advanced SQL in select, to achieve it you might use raw methods, raw methods embed provided strings directly into SQL statement. Browse Raw methods to see the list of all available raw methods.
Warning |
---|
Raw methods embed strings directly into SQL query, SQL injection protection is required |
...
Joins
Inner join
Query builder might join other tables for you. To perform an inner join (or just join for simplification) you may use the join method, the first argument is the name of the table to join, the remaining arguments define the column in the main table, the condition and the column in the table being joined.
Code Block | ||||
---|---|---|---|---|
| ||||
Code Block | ||||
| ||||
builder::table('users')
->select(['id', 'name']) // Select or add_select accept a column to select as well as array of columns
->first();
builder::table('users')
->select('id')
->add_select('name as name') // Adding as alias is supported as well and it will pass field validation check
->first();
builder::table('users')
->select('email')
->select('id') // This will override any previous select or add_select statements and replace it with "id"
->add_select('name') // This will append to "id" resulting in "id, name"
->first(); |
Will give you the following result:
id | name |
---|---|
1 | John Doe |
Code Block | ||||
---|---|---|---|---|
| ||||
builder::table('users')
->select('my field') // This will blow up and exception will be thrown here
->first(); |
Selecting aggregates
Sometimes you need to do more complex selects than just a field name, for example using aggregate functions, builder allows for it, treating it as valid column names. Most commonly aggregate functions are supported: MIN, MAX, AVG, SUM, COUNT.
Code Block | ||||
---|---|---|---|---|
| ||||
builder::table('orders') ->join('users', 'user_id ->select(['min(quantity) as min_quantity', 'id'max(quantity) // You can omit '=' condition in joins for convenience this will be equivalent to the code above ->select('*', 'users.name as user_name') as max_quantity']) ->add_select('avg(quantity) as avg_quantity') ->add_select('sum(quantity) as total_quantity') ->add_select('count(id) as orders') ->first(); |
...
Will give you the following
...
result:
idmin_quantity | usermax_idquantitygoods_item | avg_idquantity | usertotal_namequantity | 1orders |
---|---|---|---|---|
1 | 1 | 496 | John Doe |
Left join
To perform a left join use the following construct:
Right join
Full join
Cross join
Cross join is slightly different from all the previous joins as it does not take any arguments, only the table name.
Joining multiple tables
You may join multiple tables using the builder, simply call join several times on the builder object.
Will give you the following result:
order_id | user_name | item_name | 85 | 510 | 6 |
---|
Selecting subquery
It's possible to use subqueries in select statements as well:
Code Block | ||||
---|---|---|---|---|
| ||||
builder::table('users')
->select(['id', 'name', new subquery(function(builder $sub) {
$sub->from('orders')
->select('max(quantity)')
->where('user_id', 'users.id');
})->as('max_order_quantity'))
->limit(2)
->get(); |
The above query should give you the following results:
id | name | max_order_quantity | |
---|---|---|---|
1 | John Doe | A brick496 | 496 |
Complex condition joins
Sometimes you want to join tables on multiple conditions, this also possible via the builder, you would need to use the following syntax:
Alternative syntax
To define a custom alias for a joined table, you may use an alternative syntax for table field:
Sub query join
Sub query joins are possible, you will need to pass a query builder instance or subquery class instance instead of a table name to the join method. An alias must be specified for sub query joins.
Where clauses
Simple where clauses
You can use the where
method to add where conditions to your query. Where
accepts three arguments:
- Name of column
- The operator
[=, in, <, > , <> or !=, <=, >=, like, ilike, ...]
- The value to evaluate against the column
...
2 | Jane Doe | 3 |
If this is still not sufficient, then you might resort to the raw versions of the select methods:
Selecting raw statement
When you use select statement the field that you are attempting to select is validated against a regular expression "a-zA-Z-_|*" (it actually is a bit more complex to allow for aliases, aggregate functions and prefixes - more on that later). Sometimes though you might need to use more advanced SQL in select, to achieve it you might use raw methods, raw methods embed provided strings directly into SQL statement. Browse Raw methods to see the list of all available raw methods.
Warning |
---|
Raw methods embed strings directly into SQL query, SQL injection protection is required |
Code Block | ||||
---|---|---|---|---|
| ||||
builder::table('users') ->where ->select_raw('status'id, '=', 1) ->fetchname, :character as character', ["character" => "Good"]) ->first(); //When builder::table('users') ->where('votes', '<=' 50) ->where('status', '=', 1') ->fetch(); |
You can only supply two arguments, the column and the value. In this case depending on the value type, the operator defaults to =
or in
:
Section | |||||||||
---|---|---|---|---|---|---|---|---|---|
Code Block | |||||||||
| |||||||||
Column | | ||||||||
| |||||||||
Code Block | |||||||||
|
Column | |||||||||
---|---|---|---|---|---|---|---|---|---|
| |||||||||
|
OR statements
You can chain where clauses as well as add or_ clauses to the query. The or_where
function accepts the same arguments as the where
function.
// WHERE name = 'John Doe' OR name = 'Jane Doe' $users =(raw)sql ->first(); // Raw functions work the same way as regular select and add_select, // meaning that calling select_raw will override your select statement. // Calling add_select_raw will append to your select statement, // it will add a comma delimiter, you don't need to manage it manually // You may also combine raw and non-raw statements builder::table('users') ->where ->select('nameid', 'John Doe) ->add_select('name') ->or_where('name ->add_select_raw(':character as character', ['Jane Doe') ->fetch(); |
Nested where clauses / parameter grouping
...
character' => 'Good'])
->first(); |
id | name | character |
---|---|---|
1 | John Doe | Good |
Joins
Inner join
Query builder might join other tables for you. To perform an inner join (or just join for simplification) you may use the join method, the first argument is the name of the table to join, the remaining arguments define the column in the main table, the condition and the column in the table being joined.
Code Block | ||||||
---|---|---|---|---|---|---|
| // WHERE status = 1 AND (name = 'John Doe' OR name = 'Jane Doe')
$does =
| |||||
builder::table('usersorders') ->where>join('statususers', 1) ->where(function (builder $builder) { 'user_id', '=', 'id') // Don't be alarmed that there is naming collision, the columns will be auto-prefixed automatically $builder->where>select('name*', 'John Doeusers.name as user_name') // By default nothing from a joined table will be selected though, you need to ->or_where('name', 'Jane Doe')append that manually })->fetch>first(); |
Additional where clauses
where_field / or_where_field
This function allows to compare two columns. Both fields are passed through a standard regex field name check here, that should prevent you from basic SQL injections.
Code Block | ||
---|---|---|
| ||
// WHERE last_login = created $does = // Same result builder::table('usersorders') ->where_field('last_login>join('users', 'user_id', 'createdid') // You can omit ->fetch(); // WHERE last_login > created $does = builder::table('users') ->where_field('last_login', '>', 'created')'=' condition in joins for convenience this will be equivalent to the code above ->select('*', 'users.name as user_name') ->fetch>first(); |
where_null / or_where_null / where_not_null / or_where_not_null
...
The above code will give you the following data selected:
id | user_id | goods_item_id | quantity | user_name |
---|---|---|---|---|
1 | 1 | 1 | 496 | John Doe |
Left join
To perform a left join use the following construct:
Code Block | ||||||
---|---|---|---|---|---|---|
| // WHERE last_login IS NULL
$does = builder
| |||||
builder::table('usersorders') ->where_null('last_login') ->fetch(); // WHERE last_login IS NOT NULL $does = ->left_join('users', 'user_id', '=', 'id') ->select('*', 'users.name as user_name') ->first(); |
Right join
Code Block | ||||
---|---|---|---|---|
| ||||
builder::table('usersorders') ->where_not_null('last_login') ->fetch(); |
where_in / or_where_in / where_not_in / or_where_not_in
This is a shortcut for ->
where('column', 'in', $value);
where_like / or_where_like / where_like_starts_with / or_where_like_starts_with / where_like_ends_with / or_where_like_ends_with
The builder provides some shortcuts for LIKE statements.
Section | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
By default the like comparisons are case-sensitive. If you need case-insensitive LIKE statements use: ilike, ilike_starts_with, ilike_ends_with
If you need NOT LIKE statements use: !like, !like_starts_with, !like_ends_with, !ilike, !ilike_starts_with, !ilike_ends_with
where_like_raw / or_where_like_raw
If none of the other like functions cover you need you can use the raw methods. The value won't be surrounded by any percentage signs, this needs to be handled by yourself.
Warning |
---|
If you use these function you'll insert a raw LIKE statement so it has a potential for introducing SQL injections. Also make sure you escape the values passed to it using $DB->sql_like_escape() . |
Code Block | ||
---|---|---|
| ||
$does = builder::table('users')
->where_like_raw('name', 'John%Doe')
->fetch();
|
where_exists / or_where_exists / where_not_exists / or_where_not_exists
The where_exists functions allows for creating a WHERE EXISTS (subquery) condition. It accepts a builder or a Closure argument, which will receive a new builder instance. This allows you to define the query that should be used inside the exists statement.
Code Block | ||
---|---|---|
| ||
// SELECT * // FROM orders // WHERE EXISTS (SELECT id FROM users WHERE name LIKE '%Doe' AND id = orders.user_id) $second_builder = builder::table('users') ->select('id') ->where('name', 'like_ends_with', 'Doe') ->where_field('users.id', 'orders.user_id'); $orders ->right_join('users', 'user_id', '=', 'id') ->select('*', 'users.name as user_name') ->first(); |
Full join
Code Block | ||||
---|---|---|---|---|
| ||||
builder::table('orders')
->full_join('users', 'user_id', '=', 'id')
->select('*', 'users.name as user_name')
->first(); |
Cross join
Cross join is slightly different from all the previous joins as it does not take any arguments, only the table name.
Code Block | ||||
---|---|---|---|---|
| ||||
builder::table('orders')
->cross_join('users')
->first(); |
Joining multiple tables
You may join multiple tables using the builder, simply call join several times on the builder object.
Code Block | ||||
---|---|---|---|---|
| ||||
builder::table('orders')
->join(['users', 'users'], 'user_id', 'id')
->join('goods', 'goods_item', 'id')
->select(['orders.id as order_id', 'users.name as user_name', 'goods.name as item_name', 'orders.quantity as quantity'])
->first(); |
Will give you the following result:
order_id | user_name | item_name | quantity |
---|---|---|---|
1 | John Doe | A brick | 496 |
Complex condition joins
Sometimes you want to join tables on multiple conditions, this also possible via the builder, you would need to use the following syntax:
Code Block | ||||
---|---|---|---|---|
| ||||
builder::table('orders')
->join('users', function(builder $joining, builder $builder) {
$joining->where_field('orders.id', 'users.id') // You can add as many where conditions as you want here
->where('users.name', '!=', 'Justin Bieber'); // Nah, no orders here
})
->first(); |
Alternative syntax
To define a custom alias for a joined table, you may use an alternative syntax for table field:
Code Block | ||||
---|---|---|---|---|
| ||||
use core\orm\query\table;
// Using table class
builder::table('orders')
->join((new table('users'))->as('u'), 'user_id', 'id')
->select('*', 'u.name as user_name')
->first();
// Or using array syntax
builder::table('orders')
->join(['users', 'u'], 'user_id', 'id')
->select('*', 'u.name as user_name')
->first();
// They both will achieve the same result
// If you examine the code, the second one internally will
// be translated to the first one, saving you from a cumbersome syntax constructs |
Sub query join
Sub query joins are possible, you will need to pass a query builder instance or subquery class instance instead of a table name to the join method. An alias must be specified for sub query joins.
Code Block | ||||
---|---|---|---|---|
| ||||
use core\orm\query\table; use core\orm\query\subquery; $sub_query = builder::table('ordersusers') ->where_exists($second_builder) ->fetch(('status', 1); // AlternativeA $ordersbasic =way builder::table('orders') ->where_exists(function(builder $builder) { $builder ->join((new table($sub_query))->as('u'), 'user_id', 'id') ->select('id*'), 'u.name as user_name') ->first(); // Using short syntax ->where('name'builder::table('orders') ->join([$sub_query, 'u'], 'likeuser_ends_withid', 'Doeid') ->where_field('users.id ->select('*', 'ordersu.name as user_idname'); })->fetch(); |
Raw where clauses
If you need to pass a raw condition to your query you can either pass an instance of SQL to your clause:
Code Block | ||
---|---|---|
| ||
$sql = new sql("name = :name", ['name' => 'John']); $users = ->first(); // Using subquery class builder::table('usersorders') ->where($sql) ->fetch(); |
Alternative, use the provided raw methods, passing the SQL part as first and the params as second argument:
where_raw
or_where_raw
Code Block | ||
---|---|---|
| ||
$users = builder::table('users')
->where_raw("name = :name", ['name' => 'John'])
->fetch();
|
Warning |
---|
Raw methods embed strings directly into SQL query, SQL injection protection is required. |
Complex example
Section | ||
---|---|---|
Column | | width | 50%
Where clauses
Simple where clauses
You can use the where
method to add where conditions to your query. Where
accepts three arguments:
- Name of column
- The operator
[=, in, <, > , <> or !=, <=, >=, like, ilike, ...]
- The value to evaluate against the column
Where
provides a fluent interface and can be chained. By default the value will always end up as a named parameter, means it's protected against SQL injection.
Code Block | ||
---|---|---|
| ||
// WHERE (name = 'John Doe' OR name = 'Jane Doe') AND (status = 1 OR last_login IS NULL) AND email LIKE '%gmail.com'
$users =builder::table('users') ->where('status', '=', 1) ->fetch(); builder::table('users') ->where(function (builder $builder ) {
$builder->where(' namevotes', '<='John Doe')
50) ->or_where >where('name status', '='Jane ,Doe'); } 1') ->where(function (builder $builder) { $builder >fetch(); |
You can only supply two arguments, the column and the value. In this case depending on the value type, the operator defaults to =
or in
:
Section | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
|
Group by multiple columns
If you need to group results by multiple columns, builder allows you to do that doing multiple calls to group_by method.
Builders also allows you to reset any order condition added, by calling reset_group_by()
method or passing null
to the group_by()
method.
Group by raw value
If you need some more complex grouping, you may resort to a raw method group_by_raw to embed a raw string into the query. It works in a similar manner to order_by_raw and add_select_raw:
Warning |
---|
Raw methods embed strings directly into SQL query, SQL injection protection is required. |
Offsetting or limiting the results
limit()
Will add limit to the results:
Code Block | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||||||
$users
|
Order
Order by a column
It's quite easy to add order statements to the query using query builder. To simply order by a column name you should use order_by method.
It takes the column name to sort by as first argument, the second argument is optional defining order direction, ascending (asc) or descending (desc), defaulting to ascending.
This will give you the following result
...
Order by multiple columns
If you need to order by multiple columns subsequent calls to order_by will achieve that. Each call to order_by appends to order condition SQL.
This would give you the following results:
...
Builders also allows you to reset any order condition added, by calling reset_order_by()
method or passing null
to the order_by()
method.
The above code would give you the following result:
...
Info |
---|
Currently it behaves slightly differently from select methods naming wise. Calling select overrides current select SQL and calling add_select() appends to it, order_by() doesn't have add_order_by() and appends to the order condition to override it, however it allows you to call do_not_order() to reset order list. |
Order by raw statement
Similar to column names passed to select and add_select methods, column names are validated to protect you against accidental SQL injection. However if you require order by raw statement it is achievable calling order_by_raw method
Warning |
---|
Raw methods embed strings directly into SQL query, SQL injection protection is required. |
You may also use (raw) sql with order_by:
Grouping
Group by a column
If you need to group results by a column, builder allows you to do that and there is a group_by method to do that.
Should give you the following result:
...
|
OR statements
You can chain where clauses as well as add or_ clauses to the query. The or_where
function accepts the same arguments as the where
function.
Code Block | ||
---|---|---|
| ||
// WHERE name = 'John Doe' OR name = 'Jane Doe'
$users = builder::table('users')
->where('name', 'John Doe')
->or_where('name', 'Jane Doe')
->fetch(); |
Nested where clauses / parameter grouping
If you need to group where clauses you can use the nested where functionality. Just pass a Closure argument, which will receive the nested builder as an argument.
Code Block | ||
---|---|---|
| ||
// WHERE status = 1 AND (name = 'John Doe' OR name = 'Jane Doe')
$does = builder::table('users')
->where('status', 1)
->where(function (builder $builder) {
$builder->where('name', 'John Doe')
->or_where('name', 'Jane Doe')
})->fetch(); |
Additional where clauses
where_field / or_where_field
This function allows to compare two columns. Both fields are passed through a standard regex field name check here, that should prevent you from basic SQL injections.
Code Block | ||
---|---|---|
| ||
// WHERE last_login = created
$does = builder::table('users')
->where_field('last_login', 'created')
->fetch();
// WHERE last_login > created
$does = builder::table('users')
->where_field('last_login', '>', 'created')
->fetch(); |
where_null / or_where_null / where_not_null / or_where_not_null
This is a shortcut for ->
where('column', null);
Code Block | ||
---|---|---|
| ||
// WHERE last_login IS NULL $does = builder::table('users') ->limit(1) ->get(); // Usually used together with limit $users = builder::table('users') ->first(); ->where_null('last_login') ->fetch(); // CallingWHERE firstlast_login willIS addNOT limit 1 to the query |
offset()
Will add offset to the query:
Code Block | ||
---|---|---|
| ||
$usersNULL $does = builder::table('users') ->offset(1) ->get(); // Usually used together with limit $users = builder::table('users') ->offset(1) ->limit(1) ->get(); |
Warning |
---|
Due to the DB driver limitations limits can not be used within any form of subqueries. If you add a limit there, you will get a debugging notice. |
Unions
The query builder also provides easy means to unite two queries together. Here is how it works.
This what the result of $users
would be:
...
Field auto-prefixing
If you do not specify an alias on your builder then the builder automatically will use the table name (without the prefix) as the alias. You can specify your own alias by using the as()
function or pass it as second argument to the builder::table()
function.
All columns used in select, add_select, group_by, having, or_having, where, or_where, join
, etc. will automatically be prefixed with the proper alias. You can also manually specify the alias in the column with the dot-syntax (i.e. 'users.name
').
In more detail
It might seem that when using query builder and referring to fields the field prefix is not included. That's on the surface only to make it easier for developers. All the fields passed to the fielder without using raw functions which includes: select, add_select, group_by, having, or_having, where, or_where, join, etc. prefix fields with either a table name given (without moodle table prefix, e.g. mdl_). On the query builder you may specify an alias for the table the builder is for by calling as() or by passing the alias as second argument to the builder::table()
function. This will result in all fields passed to the builder to be prefixed with the given alias when SQL is generated. If however the alias is not set, the fields still will be prefixed but with the table name instead, in the form of "{table}".
For joined tables, they will be automatically aliased with the table name without moodle prefix e.g. for mdl_user it will pre prefixed with user. This behaviour is designed to allow more natural flow when building query, for example you can add where('user.name', 'John) instead of where('{user}.name', 'John). It's smart enough not to prefix fields if they are already prefixed, or add prefix correctly if as or aggregate function is specified. How it works behind the scenes, if string is passed to the method that accepts a field, it wraps it in a field() class which in turn is responsible to add prefix if needed. Field class accepts a name and a link to a builder instance it should belong to.
All these functions support overloading and you may pass a field class directly, if you didn't link it to any other builder, it will be linked to the current automatically. Field class has some built in subclasses for convenience, including raw_field and subquery (technically field is a subclass of raw field). Subquery accepts a builder and allows you to specify an alias as. The application is to use it in select, where you need to select a subquery.
...
->where_not_null('last_login')
->fetch(); |
where_in / or_where_in / where_not_in / or_where_not_in
This is a shortcut for ->
where('column', 'in', $value);
where_like / or_where_like / where_like_starts_with / or_where_like_starts_with / where_like_ends_with / or_where_like_ends_with
The builder provides some shortcuts for LIKE statements.
Section | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
By default the like comparisons are case-sensitive. If you need case-insensitive LIKE statements use: ilike, ilike_starts_with, ilike_ends_with
If you need NOT LIKE statements use: !like, !like_starts_with, !like_ends_with, !ilike, !ilike_starts_with, !ilike_ends_with
where_like_raw / or_where_like_raw
If none of the other like functions cover you need you can use the raw methods. The value won't be surrounded by any percentage signs, this needs to be handled by yourself.
Warning |
---|
If you use these function you'll insert a raw LIKE statement so it has a potential for introducing SQL injections. Also make sure you escape the values passed to it using $DB->sql_like_escape() . |
Code Block | ||
---|---|---|
| ||
$does = builder::table('users')
->where_like_raw('name', 'John%Doe')
->fetch();
|
where_exists / or_where_exists / where_not_exists / or_where_not_exists
The where_exists functions allows for creating a WHERE EXISTS (subquery) condition. It accepts a builder or a Closure argument, which will receive a new builder instance. This allows you to define the query that should be used inside the exists statement.
Code Block | ||
---|---|---|
| ||
// SELECT *
// FROM orders
// WHERE EXISTS (SELECT id FROM users WHERE name LIKE '%Doe' AND id = orders.user_id)
$second_builder = builder::table('users')
->select('id')
->where('name', 'like_ends_with', 'Doe')
->where_field('users.id', 'orders.user_id');
$orders = builder::table('orders')
->where_exists($second_builder)
->fetch();
// Alternative
$orders = builder::table('orders')
->where_exists(function(builder $builder) {
$builder->select('id')
->where('name', 'like_ends_with', 'Doe')
->where_field('users.id', 'orders.user_id');
})->fetch(); |
Raw where clauses
If you need to pass a raw condition to your query you can either pass an instance of SQL to your clause:
Code Block | ||
---|---|---|
| ||
$sql = new sql("name = :name", ['name' => 'John']);
$users = builder::table('users')
->where($sql)
->fetch();
|
Alternative, use the provided raw methods, passing the SQL part as first and the params as second argument:
where_raw
or_where_raw
Code Block | ||
---|---|---|
| ||
$users = builder::table('users')
->where_raw("name = :name", ['name' => 'John'])
->fetch();
|
Warning |
---|
Raw methods embed strings directly into SQL query, SQL injection protection is required. |
Complex example
Section | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
Order
Order by a column
It's quite easy to add order statements to the query using query builder. To simply order by a column name you should use order_by method.
It takes the column name to sort by as first argument, the second argument is optional defining order direction, ascending (asc) or descending (desc), defaulting to ascending.
Code Block | ||
---|---|---|
| ||
builder::table('orders')
->select(['id', 'name'])
->order_by('name', 'asc')
->limit(2)
->get();
|
This will give you the following result
id | name |
---|---|
2 | Jane Doe |
1 | John Doe |
Order by multiple columns
If you need to order by multiple columns subsequent calls to order_by will achieve that. Each call to order_by appends to order condition SQL.
Code Block | ||
---|---|---|
| ||
builder::table('orders')
->select(['id', 'name', 'status'])
->order_by('status', 'asc')
->order_by('name', 'asc')
->limit(2)
->get();
|
This would give you the following results:
id | name | status |
---|---|---|
7 | Stephen Monroe | 0 |
4 | Adam Welsh | 1 |
Builders also allows you to reset any order condition added, by calling reset_order_by()
method or passing null
to the order_by()
method.
Code Block | ||
---|---|---|
| ||
builder::table('orders')
->select(['id', 'name'])
->order_by('status', 'asc')
->order_by('name', 'asc')
->reset_order_by()
->order_by('id', 'desc')
->limit(2)
->get();
|
The above code would give you the following result:
id | name |
---|---|
2 | Jane Doe |
1 | John Doe |
Info |
---|
Currently it behaves slightly differently from select methods naming wise. Calling select overrides current select SQL and calling add_select() appends to it, order_by() doesn't have add_order_by() and appends to the order condition to override it, however it allows you to call do_not_order() to reset order list. |
Order by raw statement
Similar to column names passed to select and add_select methods, column names are validated to protect you against accidental SQL injection. However if you require order by raw statement it is achievable calling order_by_raw method:
Code Block | ||
---|---|---|
| ||
builder::table('orders')
->select(['id', 'name'])
->order_by_raw('id asc, name desc')
->limit(2)
->get();
|
Warning |
---|
Raw methods embed strings directly into SQL query, SQL injection protection is required. |
You may also use (raw) sql with order_by:
Code Block | ||
---|---|---|
| ||
use core\dml\sql;
builder::table('orders')
->select(['id', 'name'])
->order_by(new sql('name asc'))
->limit(2)
->get();
|
Grouping
Group by a column
If you need to group results by a column, builder allows you to do that and there is a group_by method to do that.
Code Block | ||
---|---|---|
| ||
builder::table('orders')
->join('users', 'id', 'user_id')
->select(['users.name as user_name', 'max(quantity) as max_quantity'])
->group_by('users.name')
->order_by('max(quantity)')
->limit(2)
->get();
|
Should give you the following result:
user_name | max_quantity |
---|---|
John Doe | 496 |
Ivan Svobodin | 5 |
Group by multiple columns
If you need to group results by multiple columns, builder allows you to do that doing multiple calls to group_by method.
Code Block | ||
---|---|---|
| ||
builder::table('orders')
->join('users', 'id', 'user_id')
->select(['users.name as user_name', 'max(quantity) as max_quantity', 'orders.id'])
->group_by('users.name')
->group_by('id')
->order_by('max(quantity)')
->limit(2)
->get();
|
Builders also allows you to reset any order condition added, by calling reset_group_by()
method or passing null
to the group_by()
method.
Group by raw value
If you need some more complex grouping, you may resort to a raw method group_by_raw to embed a raw string into the query. It works in a similar manner to order_by_raw and add_select_raw:
Code Block | ||
---|---|---|
| ||
builder::table('orders')
->join('users', 'id', 'user_id')
->select(['users.name as user_name', 'max(quantity) as max_quantity'])
->group_by_raw('"users".name')
->order_by('max(quantity)')
->limit(2)
->get();
|
Warning |
---|
Raw methods embed strings directly into SQL query, SQL injection protection is required. |
Offsetting or limiting the results
limit()
Will add limit to the results:
Code Block | ||
---|---|---|
| ||
$users = builder::table('users')
->limit(1)
->get();
// Usually used together with limit
$users = builder::table('users')
->first(); // Calling first will add limit 1 to the query |
offset()
Will add offset to the query:
Code Block | ||
---|---|---|
| ||
$users = builder::table('users')
->offset(1)
->get();
// Usually used together with limit
$users = builder::table('users')
->offset(1)
->limit(1)
->get(); |
Warning |
---|
Due to the DB driver limitations limits can not be used within any form of subqueries. If you add a limit there, you will get a debugging notice. |
Unions
The query builder also provides easy means to unite two queries together. Here is how it works.
Code Block | ||
---|---|---|
| ||
$gmail_users = builder::table('users')
->select(['id', 'name'])
->where('email', 'like_ends_with', 'gmail.com');
$users = builder::table('users')
->select_raw('id, name')
->where('email', 'like_ends_with', 'gmail.com')
->union($gmail_users)
->get();
// You may also do union all, however that might result in debugging notice
// due to the fact that $DB always requires the first column to be unique :shrug:
$all = builder::table('users')
->select_raw('id, name')
->union_all($gmail_users)
->get();
|
This what the result of $users
would be:
id | name |
---|---|
1 | John Doe |
2 | Jane Doe |
6 | Alisson Carr |
Field auto-prefixing
If you do not specify an alias on your builder then the builder automatically will use the table name (without the prefix) as the alias. You can specify your own alias by using the as()
function or pass it as second argument to the builder::table()
function.
All columns used in select, add_select, group_by, having, or_having, where, or_where, join
, etc. will automatically be prefixed with the proper alias. You can also manually specify the alias in the column with the dot-syntax (i.e. 'users.name
').
In more detail
It might seem that when using query builder and referring to fields the field prefix is not included. That's on the surface only to make it easier for developers. All the fields passed to the fielder without using raw functions which includes: select, add_select, group_by, having, or_having, where, or_where, join, etc. prefix fields with either a table name given (without moodle table prefix, e.g. mdl_). On the query builder you may specify an alias for the table the builder is for by calling as() or by passing the alias as second argument to the builder::table()
function. This will result in all fields passed to the builder to be prefixed with the given alias when SQL is generated. If however the alias is not set, the fields still will be prefixed but with the table name instead, in the form of "{table}".
For joined tables, they will be automatically aliased with the table name without moodle prefix e.g. for mdl_user it will pre prefixed with user. This behaviour is designed to allow more natural flow when building query, for example you can add where('user.name', 'John) instead of where('{user}.name', 'John). It's smart enough not to prefix fields if they are already prefixed, or add prefix correctly if as or aggregate function is specified. How it works behind the scenes, if string is passed to the method that accepts a field, it wraps it in a field() class which in turn is responsible to add prefix if needed. Field class accepts a name and a link to a builder instance it should belong to.
All these functions support overloading and you may pass a field class directly, if you didn't link it to any other builder, it will be linked to the current automatically. Field class has some built in subclasses for convenience, including raw_field and subquery (technically field is a subclass of raw field). Subquery accepts a builder and allows you to specify an alias as. The application is to use it in select, where you need to select a subquery.
Let's get to some code examples to get better understanding of all this.
Code Block | ||
---|---|---|
| ||
// Will generate the following sql before it goes to the DML layer:
// SELECT "users".id, "users".name FROM {users} users WHERE 1 = 1
builder::table('users')
->select(['id', 'name'])
->get();
// Will generate the following sql before it goes to the DML layer:
// SELECT "u".id, "u".name FROM {users} u WHERE 1 = 1
builder::table('users')
->as('u')
->select(['id', 'name'])
->get();
// Will generate the following sql before it goes to the DML layer:
// SELECT "u".id, "u".name, "orders".quantity
// FROM {users} u
// JOIN {orders} orders ON ("u.id" = "orders".user_id)
// WHERE 1 = 1
// ORDER BY "u".name ASC
builder::table('users', 'u')
->join('orders', 'id', 'user_id')
->select(['id', 'name', 'orders.quantity'])
->order_by('name')
->get();
|
Raw methods
In addition to some query building methods query builder offers a raw version which has a suffix '_raw' and accepts two arguments: SQL and parameters. These methods embed provided sql in the appropriate part of the query.
List of available raw methods:
...