Query builder

The Database service is quite handy, but also a bit limited: you must write your queries by hand and bind the parameters. There's a more practical approach, the Query builder.

With the Query builder you can craft your queries with a fluid and expressive syntax that allows a better visual inspection of the code.

For example, this:

$database = resolve(Database::class);
$ret = $database->query("SELECT * FROM posts WHERE status = ? AND ((created > ? AND created < ?) OR created = ?)", ['Published', '2021-01-01', '2021-01-03', '2021-04-10']);

Can be written this way:

query('posts')
    ->where('status', 'Published')
    ->where(function($query) {
        # Group the two first date conditions
        $query->where(function($query) {
            $query->where('created', '2021-01-01', '>');
            $query->where('created', '2021-01-03', '<');
        });
        # The third condition is an OR
        $query->where('created', '2021-04-10', '=', 'OR');
    })
    ->all();

The Query builder supports the most common operations in a database agnostic way; each query will be compiled by the proper QueryBuilder according to the settings of that Connection.

Selecting rows

Row selection is easy, just use the query() helper to get a Query object for the specified table, set the conditions and run the query with all() to retrieve all the matching rows:

$rows = query('posts')
    ->where('status', 'Published')
    ->all();

If you only want a single row use first():

$row = query('posts')
    ->where('status', 'Published')
    ->first();

You can specify other operator than = too:

$rows = query('posts')
    ->where('status', 'Trash', '!=')
    ->all();

Order results:

$rows = query('posts')
    ->where('status', 'Trash', '!=')
    ->order('title', 'ASC')
    ->all();

Limit them:

$rows = query('posts')
    ->where('status', 'Trash', '!=')
    ->order('title', 'ASC')
    ->limit(0, 15)
    ->all();

Paginate also:

$rows = query('posts')
    ->where('status', 'Trash', '!=')
    ->order('title', 'ASC')
    ->page(1, 15)
    ->all();

And grouping:

$rows = query('posts')
    ->where('status', 'Trash', '!=')
    ->group('author', 'ASC')
    ->all();

Where, Having

The where and having methods accept up to four parameters to set conditions:

• Column
• Value
• Comparison operator
• Binary operator

$rows = query('posts')
    ->where('status', 'Published', '=', 'AND')
    ->all();

Also you may group them by using a Closure:

$rows = query('posts')
    ->where('status', 'Published')
    ->where(function($query) {
        $query->where('created', '2021-01-01', '>');
        $query->where('created', '2021-01-03', '<');
    })
    ->all();

Unions and joins

Union and Join operations are also supported by some adapters:

$other = query('migration')
    ->column('id')
    ->column('name')
    ->where('id', 1);
$rows = query('test', 't')
    ->column('id')
    ->column('name')
    ->where('id', 1)
    ->union($other)
    ->all();

And for joining tables:

$rows = query('user', 'u')
    ->join('user_meta', 'id_test', 'u.id')
    ->where('u.id', 1)
    ->all();

Or for a more complex example:

use Aurora\Database\Database;
use Aurora\Database\Query\Argument;

...

$rows = query('test', 't')
    ->join(Argument::table('test_meta', 'tm'), function($query) {
        $query->where(Argument::column('tm.id_test'), Argument::column('t.id'));
        $query->where(Argument::column('tm.name'), 'comment');
    })
    ->where('t.id', 1)
    ->all();

Notice how we use Argument::table() and Argument::column() in this example. This is required to avoid the automatic back-ticking of these parameters, you can use also Argument::method() for the built-in methods of your DBMS, for example Argument::method('NOW') on MySQL to use the NOW() function.

Aggregates

$max = query('test')->max('price');

$min = query('test')->min('price');

$sum = query('test')->sum('price');

$avg = query('test')->avg('price');

$count = query('test')->count();

Select chunked rows

query('test')->chunk(function($rows) {
    Utilities::debug($rows);
}, 20);

Inserting rows

Single row:

query('test')->insert([
    'id' => 0,
    'name' => 'foo',
    'created' => Argument::method('NOW'),
    'modified' => Argument::method('NOW')
]);

Multiple rows:

query('test')->insert([
    [
        'id' => 0,
        'name' => 'foo',
        'created' => Argument::method('NOW'),
        'modified' => Argument::method('NOW')
    ], [
        'id' => 0,
        'name' => 'bar',
        'created' => Argument::method('NOW'),
        'modified' => Argument::method('NOW')
    ]
]);

Updates

query('test')
    ->where('id', 10, '<')
    ->update(['modified' => Argument::method('NOW')]);

Upserts

query('test')->upsert(
    ['id' => 0, 'name' => 'asdasd', 'created' => Argument::method('NOW'), 'modified' => Argument::method('NOW')],
    ['modified' => Argument::method('NOW')]
);

Deleting rows

query('test')->where('id', 10, '<')->delete();

Truncating tables

query('test')->truncate();

Next up, the ORM layer.