Building queries

The find() method returns a query object. Conditions, sorting and limits are chained onto it, and at the end you call a method that runs the query. Queries have no files of their own: they are written in controllers and models.

A simple query

$posts = Post::find()
    ->where(['status' => 1])
    ->all();

The query returns an array of Post objects: all posts with status 1. The database receives SELECT * FROM post WHERE status = 1.

Sorting and limiting

$latest = Post::find()
    ->where(['status' => 1])
    ->orderBy(['created_at' => SORT_DESC])
    ->limit(10)
    ->all();

You get the ten newest published posts. SORT_DESC means descending, SORT_ASC ascending.

Kinds of conditions

Notation	Meaning
`['status' => 1, 'user_id' => 5]`	Both equalities at once
`['id' => [1, 2, 3]]`	A value from the list
`['>', 'created_at', 1767225600]`	A comparison
`['like', 'title', 'php']`	The title contains the word
`['between', 'id', 10, 20]`	A value in a range
`['not', ['slug' => '']]`	The negation of a condition

Yii inserts values into such conditions safely by itself.

Combined conditions

Conditions are added with andWhere() and orWhere(), which is handy when some of them depend on the visitor's request:

// controllers/PostController.php
public function actionSearch(string $q = ''): string
{
    $query = Post::find()->where(['status' => 1]);
 
    if ($q !== '') {
        $query->andWhere(['like', 'title', $q]);
    }
 
    return $this->render('index', ['posts' => $query->all()]);
}

The URL /post/search?q=yii shows published posts with the word "yii" in the title, and without the parameter all published posts.

How a query ends

Method	What it returns
`all()`	An array of objects
`one()`	The first object or `null`
`count()`	The number of rows
`exists()`	`true` if there is at least one row
`column()`	The values of the first selected column

The asArray() method before all() returns rows as plain arrays, which is faster for large selections: Post::find()->select(['id', 'title'])->asArray()->all().

The builder without a model

Reports do not need models; the Query class is enough:

use yii\db\Query;
 
$rows = (new Query())
    ->select(['user_id', 'total' => 'COUNT(*)'])
    ->from('post')
    ->groupBy('user_id')
    ->all();

The result is an array of rows with the number of posts of each author.

Transactions

A transaction joins several changes: either all of them happen or none.

Yii::$app->db->transaction(function () use ($post, $comment) {
    $post->save();
    $comment->post_id = $post->id;
    $comment->save();
});

If an exception is thrown inside the function, all changes are rolled back.

Common mistake

A condition assembled as a string from visitor data, where("title = '$q'"), opens the door to SQL injection: the visitor can append their own code to the query. Use the array notation or parameters: where('title = :title', [':title' => $q]).

Thanks for reading!