Query builder

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.

Location

ORM and its dependencies are located under \core\orm namespace. The builder itself lives in \core\orm\query. Feel free to examine the code.

For the purpose of the examples below it's assumed that there is a use statement for the builder class:

use \core\orm\query\builder;

Example database

For the purpose of any examples in this document, the following tables will be used:

Retrieving results

Retrieving from a table

It's very simple to select results from a table, all the examples following in the document will be selecting records from a table.

// You can instantiate a builder to operate on a table using a factory method "table"
builder::table('table_name')
	->...;

// If you prefer to new up a class in a conventional way, you can do the following:
(new builder())
	->from('table_name', 'optional_alias') // The alias defaults to the table name if not set
	->...;

Retrieving from a subquery

As an alternative to retrieving results from a table, you might select results from a subquery (represented by another builder instance).

$sub_query = builder::table('user', 't1')
    ->where('status', 1);

// You must set an alias for the subquery, otherwise an exception will be thrown
$user = builder::table($sub_query)
    ->as('sq')
    ->where('name', 'like', 'John Doe')
    ->first();

// Alternatively:
$query = new builder();
$user = $query->from($sub_query)
    ->as('sq')
	->where('name', 'like', 'John Doe')
	->first();

// The latter will give you the following SQL (might differ slightly from db to db)
//
// SELECT "sq".* 
// FROM (SELECT * FROM {user} "t1" WHERE status = :p_1) sq 
// WHERE "sq".name LIKE :p_2 
// LIMIT 1; [:p_1 => 1, :p_2 => '%John Doe%']

Retrieve a single row

find(id) / find_or_fail(id)

Returns a single row identified by passed ID. If the row does not exist in the table it returns null. It does ignore any other conditions, joins, unions, etc. and always fetches all columns for the row.

find_or_fail() works the same except that it throws an exception when row does not exist.

$user = builder::table('users')->find(1);

// Will throw exception if record is not found
$user = builder::table('users')->find_or_fail(1);

$user = $DB->get_record('users', ['id' => 1]);

// Will throw exception if record is not found
$user = $DB->get_record('users', ['id' => 1], '*', MUST_EXIST);

first() / first_or_fail()

Returns the first row of the result. You have to specify order_by() if you want to use first(). You cannot use limit() in conjunction with first().

first_or_fail() fails with an exception if there was no records found. 

$user = builder::table('users')
    ->where('status', 1)
    ->order_by('votes', 'desc')
    ->first();

// Will throw exception if no records found
$user = builder::table('users')
    ->where('name', 'like', 'Bob')
    ->order_by('votes', 'desc')
    ->first_or_fail();

$user = $DB->get_records('users', ['status' => 1], 'votes DESC', '*', 0, 1);

one()

Returns only one row assuming that the row is unique with given parameters. Will throw an exception if multiple rows exist. You cannot use offset() or limit() in conjunction with one().

Accepts a boolean $strict argument which makes the query fail with an exception if there are no records found.

$user = builder::table('users')
    ->where('name', 'John Doe')
    ->one();

$user = $DB->get_record('users', ['name' => 'John Doe']);

Retrieving multiple rows

fetch()

Fetches all rows of the table or if conditions are present, all rows matching the given conditions. By default returns an array of stdClass instances. The type of result can be changed either to an array or any other object by using the map_to() function.

// Returns all results from the table
$users = builder::table('users')->fetch();

$users = builder::table('users')
    ->where('status', 1)
    ->fetch();

// Return an array of arrays
$users = builder:::table('users')
    ->results_as_arrays()
    ->fetch();

$users = $DB->get_records('users'); 

$users = $DB->get_records('users', ['status' => 1]);

fetch_recordset() / get_lazy()

Returns a lazy_collection instance which extends moodle_recordset. This is useful if you want lazy loading of results. It will reduce the amount of memory used as it only loads one record at a time if you cycle through it. But on the other hand you can't count the results or use other functionality on it.

If you've used map_to() / results_as_arrays() / results_as_objects() in your query to map the items to a custom class, array or stdClass then the lazy_collection will take care of that mapping on each item.

The original recordset returned by the DML layer is passed into the lazy_collection and used internally. This is necessary to add the mapping functionality to the lazy_collection.

$users = builder::table('users')->fetch_recordset();

// Record will be loaded only once accessed
foreach ($users as $user) {
    echo $user->name;
}

$users = $DB->get_recordset('users');

// Record will be loaded only once accessed
foreach ($users as $user) {
    echo $user->name;
}

fetch_counted()

Returns the results and the total amount of items of your query.

[$users, $count] = builder::table('users')
    ->where('status', 1)
    ->offset(0)
    ->limit(5)
    ->fetch_counted();

echo "$count users found.";  // 7 users found.

// Example: will output the first 5 active users
foreach ($users as $user) {
    echo $user->name;
}

$sql = "SELECT * FROM {users} WHERE status = :status";
$count = 0; 

$users = $DB->get_counted_records_sql($sql, ['status' => 1], 0, 5, $count);

echo "$count users found.";

foreach ($users as $user) {
    echo $user->name;
}

get()

Alternative to fetch(). Always returns a collection instead of an array.

Check the collection docs for available methods.

$users = builder::table('users')->get();

paginate()

This function retrieves paginated results. It returns a paginator instance which also can be used standalone by passing a builder instance to it.

Supply the page number (starting with 1) and the number of records per page.

If you supply 0 for the page parameter it will return all results.

// Use paginator class
$query = builder::table('users');
$users = new paginator($query, 1, 5);

// Or use the fluent interface with paginate()
$users = builder::table('users')
    ->paginate(1, 5);

// paginator acts as an iterator so you can foreach over it
foreach ($users as $user) {
    echo $user->name;
}

// Prints: 5
echo $users->count();
// Prints 1
echo $users->get_page();
// Prints: 7
echo $users->get_total();

print_r($users->to_array());

/* prints:

 Array(
     'items' => Array(
         Array(
             'id' => 1,
             'name' => 'John Doe',
             ...
         ),
         ...
     ),
     'page' => 1,   // current page you are on
     'pages' => 2,  // total number of pages
     'items_per_page' => 5, 
     'next' => 1,      // next page number, null on the last page
     'prev' => null,   // previous page number, null on the first page
     'total' => 7  // total number of items
 )

*/

Other functions

value()

Returns the a single value from a record. This method will return the value of the column directly.

$email = builder::table('users')
    ->where('name', 'John Doe')
    ->value('email');

$email = $DB->get_field('users', 'email', ['name' => 'John Doe']);

count()

Returns the amount of rows for the given query.

$user_amount = builder::table('users')->count();

$user_amount = builder::table('users')
    ->where('status', 1)
    ->count();

$user_amount = $DB->count_records('users'); 

$user_amount = $DB->count_records('users', ['status' => 1]);

exists() / does_not_exist()

Returns true if the record(s) matching the current query exist / not exist.

$exists = builder::table('users')
    ->where('name', 'John Doe')
    ->exist();

$not_exists = builder::table('users')
    ->where('name', 'John Doe')
    ->or_where('name', 'Jane Doe')
    ->does_not_exist();

$exists = $DB->record_exists('users', ['name' => 'John Doe']);
$select = 'name = :name1 OR name = :name2';
$params = [
    'name1' => 'John Doe',
    'name2' => 'Jane Doe'
];
$not_exists = !$DB->record_exists_select('users', $select, $params);

Selects

Selecting a field

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.

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:

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.

builder::table('orders')
	->select(['min(quantity) as min_quantity', 'max(quantity) 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:

Selecting subquery

It's possible to use subqueries in select statements as well:

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:

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.

builder::table('users')
	->select_raw('id, name, :character as character', ["character" => "Good"])
	->first();

//When working with builder only named params are allowed, however if you need to use other types, you might use (raw)sql class.
use core\dml\sql;

builder::table('users')
	->select(new sql('id, name, ? as character, ["Good"])) // When you want to pass (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')
	->select('id')
	->add_select('name')
	->add_select_raw(':character as character', ['character' => 'Good'])
	->first();

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.

builder::table('orders')
    ->join('users', 'user_id', '=', 'id') // Don't be alarmed that there is naming collision, the columns will be auto-prefixed automatically 
    ->select('*', 'users.name as user_name') // By default nothing from a joined table will be selected though, you need to append that manually 
    ->first(); 

// Same result 
builder::table('orders') 
    ->join('users', 'user_id', 'id') // You can omit '=' condition in joins for convenience this will be equivalent to the code above 
    ->select('*', 'users.name as user_name') 
    ->first();

The above code will give you the following data selected:

Left join

To perform a left join use the following construct:

builder::table('orders')
	->left_join('users', 'user_id', '=', 'id') 
	->select('*', 'users.name as user_name')
	->first();

Right join

builder::table('orders')
	->right_join('users', 'user_id', '=', 'id')
	->select('*', 'users.name as user_name')
	->first();

Full join

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.

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.

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:

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:

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:

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.

use core\orm\query\table;
use core\orm\query\subquery;

$sub_query = builder::table('users')
				->where('status', 1);

// A basic way
builder::table('orders')
	->join((new table($sub_query))->as('u'), 'user_id', 'id')
	->select('*', 'u.name as user_name')
	->first();

// Using short syntax
builder::table('orders')
	->join([$sub_query, 'u'], 'user_id', 'id')
	->select('*', 'u.name as user_name')
	->first();

// Using subquery class
builder::table('orders')
	->join((new subquery($sub_query))->as('u'), 'user_id', 'id')
	->select('*', 'u.name as user_name')
	->first();

Where clauses

Simple where clauses

You can use the where method to add where conditions to your query. Where accepts three arguments:

1. Name of column
2. The operator [=, in, <, > , <> or !=, <=, >=, like, ilike, ...]
3. 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.

builder::table('users')
    ->where('status', '=', 1)
    ->fetch(); 

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:

$users = builder::table('users')  
    ->where('status', 1) // defaults to =    
    ->where('id', [3, 4, 5, 6]) // defaults to in()
    ->fetch();

list($sql_in, $params) = $DB->get_in_or_equal([3, 4, 5, 6], SQL_PARAMS_NAMED);
$params['status'] = 1;
$users = $DB→get_records_select('users', "status = :status AND id $sql_in", $params);

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 = 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.

// 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.

// 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);

// WHERE last_login IS NULL
$does = builder::table('users')
    ->where_null('last_login')
    ->fetch();

// WHERE last_login IS NOT NULL
$does = builder::table('users')
    ->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.

// WHERE name LIKE '%John%'
$users = builder::table('users')  
    ->where_like('name', 'John')    // ->where('name', 'like', 'John')
    ->fetch();

// WHERE name LIKE 'John%'
$users = builder::table('users')  
    ->where_like_starts_with('name', 'John')    // ->where('name', 'like_starts_with', 'John')
    ->fetch();

// WHERE name LIKE '%John'
$users = builder::table('users')  
    ->where_like_ends_with('name', 'Doe')    // ->where('name', 'like_ends_with', 'Doe')
    ->fetch();

$like_sql = $DB->sql_like('name', ':name');
$params = ['name' => '%'.$DB->sql_like_escape('John').'%'];
$users = $DB->get_records_select('users', $like_sql, $params);

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.

$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.

// 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:

$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

$users = builder::table('users')
    ->where_raw("name = :name", ['name' => 'John'])
    ->fetch();

Complex example

// 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(function (builder $builder) {
        $builder->where('name', 'John Doe')
            ->or_where('name', 'Jane Doe');
    })
    ->where(function (builder $builder) {
        $builder->where('status', 1)
            ->or_where_null('last_login');
    })
    ->where_like_ends_with('email', 'gmail.com')
    ->fetch();

$select = "(name = :name1 OR name = :name2) AND (status = :status OR last_login IS NULL) AND $like_sql";
$params = [
    'name1' => 'John Doe',
    'name2' => 'Jane Doe',
    'status' => 1
];

$like_sql = $DB->sql_like('email', ':email');
$params['email'] = '%'.$DB->sql_like_escape('gmail.com');

$users = $DB→get_records_select('users', "status = :status AND id $sql_in", $params);

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.

builder::table('orders')
	->select(['id', 'name'])
	->order_by('name', 'asc')
	->limit(2)
	->get();

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.

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:

Builders also allows you to reset any order condition added, by calling reset_order_by() method or passing null to the order_by() method.

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:

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:

builder::table('orders')
	->select(['id', 'name'])
	->order_by_raw('id asc, name desc')
	->limit(2)
	->get();

You may also use (raw) sql with order_by:

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.

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:

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.

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:

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();

Offsetting or limiting the results

limit()

Will add limit to the results:

$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:

$users = builder::table('users')
	->offset(1)
	->get();

// Usually used together with limit
$users = builder::table('users')
	->offset(1)
	->limit(1)
	->get();

Unions

The query builder also provides easy means to unite two queries together. Here is how it works.

$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:

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.

// 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:

• select_raw

• add_select_raw

• where_raw

• or_where_raw

• order_by_raw

• group_by_raw

• having_raw

• or_having_raw

Mapping of results

By default all the methods which retrieve results (find, find_or_fail, fetch, get, fetch_recordset, paginate, first, one) will return an instance of stdClass for a single record.

However, you can influence that behaviour and either have records returned as arrays or as any custom objects.

Map to array()

Just call results_as_arrays() anytime before calling one of the methods mentioned above.

$users = builder::table('users')
    ->results_as_arrays()
    ->fetch();

Map to stdClass

This is the default behaviour which will make sure each record is represented by a stdClass instance.

$users = builder::table('users')
    ->results_as_objects()
    ->fetch();

Map to custom objects

Use the map_to() function to map to your own objects. The map_to function either accepts a Closure or a class name, which it will create instances of when retrieving the results. It will pass the result from the database as an argument to the Closure or as into the constructor of the given class.

class my_custom_user {
    private $record;

    public function __construct($record) {
        $this->record = $record;
    }
}

// Returns an array of new my_custom_user instances
$users = builder::table('users')
    ->map_to(my_custom_user::class)
    ->fetch();

// Or use a Closure

$users = builder::table('users')
    ->map_to(function ($record) {
        // do something with the record
        return new my_custom_user($record); 
    })->fetch();

You can combine results_as_arrays() and map_to() if you want your custom class accept the record as an array rather than a stdClass instance.

Conditional Clauses

When \  Unless

Sometimes you want to conditionally include clauses in your query.

Rather than doing something like:

$builder = builder::table('users');

if ($only_active) {
    $builder->where('status', 1);
}

$users = $builder->fetch();

You can use the when() / unless() functions. They accept a condition as first parameter and a closure as second parameter. The Closure will receive the current builder as argument.

The Closure is only called if the condition returns true. Use unless() for the opposite behaviour.

This allows for a more fluent query code without interruptions.

// if ($only_active) { ...
$users = builder::table('users')
    ->when($only_active, function (builder $builder) {
        $builder->where('status', 1);
    })->fetch();

// if (!$only_active) { ...
$users = builder::table('users')
    ->unless($only_active, (builder $builder) {
        $builder->where('status', 0);
    })->fetch();

Both functions also accept an equivalent of an else statement as third argument which is only called if the condition is not true / false:

// if ($only_active) { ... } else { ... }
$users = builder::table('users')
    ->when($only_active, function (builder $builder) {
        $builder->where('status', 1);
    }, function (builder $builder) {
        $builder->where('status', 0);   
    })->fetch();

Sometimes an unconditional when() (->when(true, callable)) can be helpful. You can use this to group your builder statements, or if you are building groups of statements and then joining them together.

// It might be useful for debugging purposes as well as if you have one fluent flow 
// and you need a builder reference without braking the flow.
$users = builder::table('users')
	->where('status', 1)
    ->where('votes', '>', 50)
    ->when(true, function (builder $builder) {
		var_dump($builder);
    })
	->where('name', 'like', 'John')
	->fetch();

Modifying data

The builder can not only be used to retrieve data but also to modify data.

Insert

Use the insert() method to insert new rows into the table. No further conditions or query parts can be used.

// You can pass an array
$user = [
    'name' => Jack Doe',
    'status' => 1,
    'password' => 'dfsdf3w2@#@#$WREDSfds',
    'email' => 'jack.doe@gmail.com'
];

builder::table('users')->insert($user);

// You can also pass an instance of stdClass
$user = new stdClass();
$user->name = 'Jack Doe';
$user->status = 1;
$user->password = 'dfsdf3w2@#@#$WREDSfds';
$user->email = 'jack.doe@gmail.com';

$id = builder::table('users')->insert($user);

$user = new stdClass();
$user->name = 'Jack Doe';
$user->status = 1;
$user->password = 'dfsdf3w2@#@#$WREDSfds';
$user->email = 'jack.doe@gmail.com';

$id = $DB->insert_record('users', $user);

Update

You can update multiple rows in a table by using the update() method. Please note that update does not accepts an array/object containing an ID. If you want to update a single record you can either use the where condition or use update_record().

You can only use where() conditions for update().

// You can pass an array
$data = [
    'status' => 0,
    'password' => 'reset_password'
];

builder::table('users')
    ->where('name', 'like', 'Doe')
    ->where('status', 1)
    ->update($data);

$data = [
    'status' => 0,
    'password' => 'reset_password'
];

$like_sql = $DB->sql_like('name', ':name');
$params = ['name' => '%'.$DB->sql_like_escape('Doe').'%'];
$select = "$like_sql AND status = 1";

$DB->set_fields_select('users', $data, $select, $params);

Update Record

You can update a single record in two ways, first using →update() in conjunction with a proper where condition or ->update_record().

// You can pass an array of fields to update and use where()
$data = [
    'status' => 0,
    'password' => 'reset_password'
];

builder::table('users')
    ->where('id', 1)
    ->update($data);

// Or pass a record containing the id to update_record()
$data = [
    'id' => 1,
    'status' => 0,
    'password' => 'reset_password'
];

builder::table('users')->update_record($data);

$data = [
    'id' => 1,
    'status' => 0,
    'password' => 'reset_password'
];

$DB->update_record('users', $data);

Delete

You can delete multiple records in table by using the delete() method.

You can only use where() conditions for delete().

builder::table('users')
    ->where('name', 'like', 'Doe')
    ->where('status', 1)
    ->delete();

$like_sql = $DB->sql_like('name', ':name');
$params = ['name' => '%'.$DB->sql_like_escape('Doe').'%'];
$select = "$like_sql AND status = 1";

$DB->delete_records_select('users', $select, $params);

Differences with Laravel's query builder

Missing where methods

At this stage we don't have the following methods for some reason or another:

• where_between / or_where_between and where_not_between / or_where_not_between: Haven't been implemented yet, don't see any reason why not, they should just translate to two wheres internally
• whereDate / whereMonth / whereDay / whereYear / whereTime: Haven't been implemented, they require some more investigation, due to the fact that they use internal database functions, might be doable with some additions to DML layer
• where_column / or_where_column: Originally implemented as (or_)where_field, we might revisit it, p.s. generally what Laravel refers to as column we refer to as field in quite a lot of places
• JSON where clauses: Require support at the DML level, by the time of writing all database engines we use have built-in json support with a slightly different syntax

Missing features

• Change locking type: Query builder provides a way to use shared locks \ locks for update, this implementation leaves that out to DML
• Increment \ Decrement: Two methods that take field (column) name as argument and increment or decrement a given field respectively according to a given where clause
• update_or_insert: Not implemented yet, it takes two arrays as arguments, it uses first to query a record from database and then if not found it inserts it with attributes from both arrays or either if found, it updates the record with attributes from a second array
• in_random_order: Not implemented, requires support on a DML level, as SQL varies slightly from dbms to dbms. To simplify what it does, it adds ORDER BY RANDOM()

Behavioural differences

• In Laravel select and add_select functions take multiple arguments to add multiple columns to select, in the current implementation the functions take one argument either as string/field object or an array of strings / field objects to select multiple fields in one statement
• In the current implementation field prefixing is more advanced

See more about Laravel's query builder on their website.