Dorokhov.codes

Query Builder

When we are talking about Query Builder, we mean the yii\db\Query class.

It is used only for selecting data from the database.

$query = new Query;

$query->select('id, name')
    ->from('user')
    ->limit(10);

$rows = $query->all();

where()

$query->where('status=1');

// or use parameter binding to bind dynamic parameter values
$query->where('status=:status', [':status' => $status]);

// raw SQL using MySQL YEAR() function on a date field
$query->where('YEAR(somedate) = 2023');

// ...WHERE (`status` = 10) AND (`type` IS NULL) AND (`id` IN (4, 8, 15))
$query->where([
    'status' => 10,
    'type' => null,
    'id' => [4, 8, 15],
]);

Indexing Query Results

// returns [100 => ['id' => 100, 'username' => '...', ...], 101 => [...], 103 => [...], ...]
$query->from('user')
    ->indexBy('id')
    ->all();

$query>from('user')
    ->indexBy(function ($row) {
        return $row['id'] . $row['username'];
    })->all();