How to create \yii\db\Query object from plain sql query?

Yii 2.0 General Discussions
1

Is there a way to create \yii\db\Query object from plain sql query, not using query builer ?

2

At Slack you mentioned that this is for batch query. You could use that modified class:

<?php

namespace app\db;

use yii\base\Component;
use yii\db\BatchQueryResult;

class SqlBatchQueryResult extends BatchQueryResult
{
    public $sql;
    public $params = [];

    protected function fetchData()
    {
        if ($this->_dataReader === null) {
            if ($this->query) {
                $this->_dataReader = $this->query->createCommand($this->db)->query();
            } else {
                $this->_dataReader = $this->db->createCommand($this->sql, $this->params)->query();
            }
        }

        return $this->getRows();
    }
}

Usage:

foreach (
    Yii::createObject([
        'class' => \app\db\SqlBatchQueryResult::className(),
        'sql' => 'SQL here',
        'batchSize' => $batchSize,
        'db' => Yii::$app->db,
        'each' => false,
    ]) as $rows) {
    // do something with rows
}

I haven’t tested it and I write it just as-is without IDE help, so I’m not sure if there are some errors or not.

1 Like
3

Yes, that could work, but they made __dataReader private (
Thanks

4

Created own simple unbuffered row fetcher:

<?php
namespace app\components\db;

use \Yii;
use \PDO;
use yii\base\Component;

class UnbufferedQueryResultFetcher extends Component
{
    public $sql;
    public $params;
    /** @var PDO */
    private $pdo;

    public function init()
    {
        parent::init();

        $this->pdo = new PDO(
            Yii::$app->db->dsn,
            Yii::$app->db->username,
            Yii::$app->db->password
        );
        $this->pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
        $this->pdo->exec('SET NAMES ' . $this->pdo->quote(Yii::$app->db->charset));
    }

    /**
     * Returns next row from result set
     * @return \Generator
     */
    public function getIterable()
    {
        $stmt = $this->pdo->prepare($this->sql);
        $stmt->execute($this->params);
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            yield $row;
        }
        $this->pdo = null; // close connection
    }
}
5

Aw, right, more needs to be extended. Good that you solve it anyway.