Yii2 database layer: Active Record, Query Builder, and DAO

Yii offers three levels of abstraction for working with a database:

Level	Class	When to use
Active Record	`yii\db\ActiveRecord`	Records as objects, relations, validation, events.
Query Builder	`yii\db\Query`	Composing SELECT queries without writing raw SQL.
DAO / Command	`yii\db\Command`	Raw SQL with parameter binding.

Inspecting the generated SQL

Append ->createCommand()->sql to an ActiveQuery to see the SQL it produces:

echo TransactionProfit::find()->groupBy('transaction_id')->createCommand()->sql;

getRawSql() returns the query with the parameter values already substituted, which is handy for debugging:

$query = self::find()->where(['id' => $leads_ids]);

$sql = $query->createCommand()->getRawSql();

An array value produces an IN condition:

SELECT * FROM `lead` WHERE `id` IN ('254', '259', '338', '412', '590')

Aggregate methods can be called on a relational query:

public function getBestPrice()
{
    return $this->hasOne(Cost::class, ['domain_id' => 'id'])->min('value');
}