How to inject sql hints into the query with yii2

Question

Is there any way to inject a SQL hint into ActiveRecord sql query before execution? Like adding a comment before the SQL (/* query id */ SELECT ...).

This can be done by manipulating yii\db\Command with a raw SQL, but how to do that with ActiveRecord?

Something like this would be perfect:

Model::find()->withComment('query id')

Answer 1

Create CustomActiveRecord class

class CustomActiveRecord \yii\db\ActiveQuery {
    public function all($db = null, $comment = null)
    {
        if ($this->emulateExecution) {
            return [];
        }

        $command = $this->createCommand($db, $comment);

        $rows = $command->queryAll();

        return $this->populate($rows);
    }    public function one($db = null, $comment = null)
    {
        if ($this->emulateExecution) {
            return false;
        }
        $command = $this->createCommand($db, $comment);
        $row =  $command->queryOne();
        if ($row !== false) {
            $models = $this->populate([$row]);
            return reset($models) ?: null;
        }

        return null;
    }
    public function createCommand($db = null, $comment = null)
    {
        /* @var $modelClass ActiveRecord */
        $modelClass = $this->modelClass;
        if ($db === null) {
            $db = $modelClass::getDb();
        }

        if ($this->sql === null) {
            list($sql, $params) = $db->getQueryBuilder()->build($this);
        } else {
            $sql = $this->sql;
            $params = $this->params;
        }

        if ($comment) {
            $comment = str_replace(['/*', '*/'], '', $comment);
            $sql = "/*$comment*/ " . $sql;
        }
        
        $command = $db->createCommand($sql, $params);
        $this->setCommandCache($command);

        return $command;
    }

}

Your model

class User extends CustomActiveRecord {
...
}

and use

User::find()->all(comment: "CMMNT")

query /*CMMNT*/ SELECT * FROM user;