where to set SQL_BIG_SELECTS in yii2 query?

Question

I am getting error while executing my query .

SQLSTATE[42000]: Syntax error or access violation: 1104 The SELECT would examine more than MAX_JOIN_SIZE rows

I have to SET SQL_BIG_SELECTS=1.

I am using YII2 and don't know where to set this .

Please help.

Answer 1

From docs

If you need to execute a SQL query right after establishing a connection (e.g., to set the timezone or character set), you can do so in the [[yii\db\Connection::EVENT_AFTER_OPEN]] event handler.

return [
    // ...
    'components' => [
        // ...
        'db' => [
            'class' => 'yii\db\Connection',
            // ...
            'on afterOpen' => function($event) {
                // $event->sender refers to the DB connection
                $event->sender->createCommand("SET SQL_BIG_SELECTS = 1")->execute();
            }
        ],
    ],
    // ...
];

Or run SQL query once before your query:

$connection = \Yii::$app->getDb();
$res = $connection->createCommand("SET SQL_BIG_SELECTS = 1")->execute();