Query builder

In this guide, we will go through functionalities provided by SimpleAR's query builder.

The major part of this guide will explain how to use query builder to construct SELECT queries, since it is the most used query type.

We'll see how to construct other statements at the end.

Accessing builder instance

You can get a new query builder instance for a model using the query() method.

$qb = Article::query();

$qb->one(); // Or first(), last(), all().

Conditions

Simple conditions

Query builder provides a good collection of method to add conditions on data to retrieve:

Article::query()->where('author_id', 12);

Fortunately, you don't have to use query() method every time you want to build a query:

Article::where('author_id', 12);
Article::where('author_id', '=', 12); // Same as above

Attribute-value conditions

Basic where methods

MyModel::whereNot('attr', 'val'); // NOT `attr` = 'val'
MyModel::andWhere('attr', 'val'); // AND `attr` = 'val'
MyModel::orWhere('attr', 'val');  // OR  `attr` = 'val'
MyModel::andWhereNot('attr', 'val'); // AND NOT `attr` = 'val'
MyModel::orWhereNot('attr', 'val');  // OR  NOT `attr` = 'val'

Where null

$query->whereNull('author_id');
// <=>
$query->where('author_id', null);

WHERE `author_id` IS NULL

$query->whereNotNull('author_id');
// <=>
$query->whereNot('author_id', null)
// <=>
$query->where('author_id', '!=', null);

WHERE `author_id` IS NOT NULL

Conditions over attribute tuple

// (You can use where() method too.)
$query->whereTuple(['author_id', 'blog_id'], [[1,2], [1,3], [2,3]]);

WHERE (`author_id`, `blog_id`) IN ((1,2), (1,3), (2,3))

Alternative syntax:

$query->where('author_id,blog_id', [[1,2], [1,3], [2,3]])

Will produce the same as shown above.

Attribute-attribute conditions

Article::whereAttr('comment_count', '>', 'view_count');

WHERE `comment_count` > `view_count`

Nested conditions

Use the whereNested method:

Article::whereNested(function ($q) {
    $q->where('author_id', [12, 13, 14])
      ->andWhere('blog_id', 1);
})
->orWhere(function ($q) {
    $q->where('title', 'Alice in Wonderland')
      ->where('blog_id', 3);
});

WHERE (
    `author_id` IN [12, 13, 14] AND `blog_id` = 1
) OR (
    `title` = 'Alice in Wonderland' AND `blog_id` = 3
)

whereNested() is the method that handles nested conditions; but where() will get it as well (by delegating to whereNested()).

Use whatever method you prefer!

Conditions over relations

This is really easy and intuitive to perform conditions over linked models in SimpleAR. This section demonstrates it.

Here is the schema I will use in my examples:

Blog schema

Here is the corresponding classes and their relations:

class Blog extends SimpleAR\Model
{
    protected static $_relations = array(
        'articles' => array(
            'type'  => 'has_many',
            'model' => 'Article',
        )
    );
}

...

class Article extends SimpleAR\Model
{
    public static $_relations = array(
        'author' => array(
            'type'  => 'belongs_to',
            'model' => 'Author',
        ),
        'blog' => array(
            'type'  => 'belongs_to',
            'model' => 'Blog',
        ),
        'readers' => array(
            'type' => 'many_many',
            'model' => 'User'
        ),
    );
}

...

class Author extends SimpleAR\Model {}

...

class User extends SimpleAR\Model {}

Now we have everything we need, let's explore what query builder can offer!

Condition on linked model's attribute

Every attribute condition method (seen above) handles attribute of linked models.

This means that the following is valid:

// Blogs where John Doe has written articles.
Blog::where('articles/author/first_name,last_name', ['John', 'Doe']);

// Blogs where no article has been viewed more than 100 times.
Blog::whereNot('articles/view_count', '>', 100);

This syntax is valid with every method seen in Simple conditions section.

The character "/" is the default relation separator character. You can change it with queryOptionRelationSeparator configuration option.

Condition on linked models

Blog::whereHas('articles');

// With negation

Blog::whereHasNot('articles');

Article::whereHas('readers', '>', 1000);

Blog::whereHas('articles', function ($q) {
    $q->where('author_id', 12);
});

Blog::whereHas('articles/readers');

// is equivalent to:

Blog::whereHas('articles', function($q) {
    $q->whereHas('readers');
});

Aggregates

// Blogs of which articles readers' average age is greater than 30.
Blog::where(DB::avg('articles/readers/age'), '>', 30);

Smooth!

There are some built-in methods like DB::avg(): sum(), count()... But if the one you want doesn't exist, you can use fn() and give it the SQL function you want to use as first parameter:

// Blogs created before 2010.
Blog:where(DB::fn('YEAR', 'created_at'), '<', 2010);

Raw conditions

If none of the available methods is sufficient to build your query, you can use the whereRaw() method:

Author::whereRaw(DB::expr("first_name LIKE 'Joe'"));

If you have to use relations in your query, note that:

The relation must be included. To manually include a relation, use join() method;
Relations are dot-separated in the query string:
Alias for root table is _ (underscore) character.

Article::join('author')->whereRaw(DB::expr("author.first_name LIKE 'Joe' AND `_`.`views` > 100"));

Select statements

Article::where('author_id', 12)
    ->orderBy('title')
    ->limit(10)
    ->offset(20) // Or: limit(10, 20)
    ->all();

You can use one(), first(), last() instead of all() if you want just one row. By default, Query builder return all columns of your table. So if you want just some field, do it like this

Article::where('blogId', 12)
    ->all(array('author_id', 'title')); // Or one(), first(), last()

SELECT id, author_id, title FROM `articles`;

The primary key of root table are always selected

Aggregate selection

Article::where('views', '>', 1000)->count();
// 12

Article::where('views', '>', 1000)->count(DB::distinct('id'));


Article::where('views', '>', 1000)->groupBy('authorId')->count('*', 'articleNb');
// array(
//     array(
//         'authorId' => 1,
//         'articleNb' => 5,
//     ),
//     array(
//         'authorId' => 2,
//         'articleNb' => 3,
//     ),
//     ...
// );

There are several built-in aggregate methods (min, max, sum, avg, count); but if it not sufficient, you can use the generic aggregate method:

Article::where('blogId', 12)->aggregate('AVG', 'views', 'avg_views');

SELECT AVG(`views`) AS `avg_views` FROM `articles`;

If you want to add an aggregate to the selection but don't want to fetch only that, use addAggregate() method. Its signature is the same as aggregate() but instead of executing the query, it add the aggregate to the list of columns to select.

Insert statements

User::insert()->fields(['first_name', 'last_name'])->values(
    ['John', 'Doe'],
    ['Karl', 'Marx']
)->run();

Update statements

User::update()->set('first_name', 'Foo')->where('last_name', 'Bar')->run();

Delete statements

Author::delete()->whereHasNot('articles')->run();

Other statements

GROUP BY

$query->groupBy('authorId');
// OR
$query->groupBy(array('authorId', 'first_name'));

GROUP BY `author_id`
/* multiple group by */
GROUP BY `author_id`, `first_name`

ORDER BY

The default sort direction is ASC

$query->orderBy('author_id');
// Multiple order by
$query->orderBy(array('author_id', 'first_name'));

ORDER BY `author_id` ASC
/* Multiple order by */
ORDER BY `author_id` ASC, `first_name` ASC

You change the sort direction like this:

$query->orderBy(array('author_id' => 'DESC'));
// Multiple order by
$query->orderBy(array('author_id', 'first_name' => 'DESC'));

ORDER BY `author_id` DESC
/* Multiple order by */
ORDER BY `author_id` ASC, `first_name` DESC

You can use some function like RAND

$query->orderBy(DB::fn('RAND'));
// OR
$query->orderBy(array(DB::fn('YEAR', 'created_at') => 'DESC'));

ORDER BY RAND()
/* OR */
ORDER BY YEAR('created_at') DESC