Getting a raw SQL query with variables in Yii1

Question

Is Yii1 has any native methods to get the raw SQL with variables built?

I try to get a complex query built on a few subqueries using CDbExpression and CommandBuilder. I got this as a result:

SELECT * FROM `news` `t` WHERE id IN (:ycp0, :ycp1, :ycp2, :ycp3, :ycp4) LIMIT 5

The dump of the criteria content:

CDbCriteria Object (
  [select] => *
  [condition] => id IN (:ycp0, :ycp1, :ycp2, :ycp3, :ycp4)

  ...

  [params] => Array(
    [:ycp0] => CDbExpression Object(
      [expression] => SELECT id FROM `news` `t` WHERE (rubric=:rb1) AND (:im2 & `im`=:im2) LIMIT 1
      [params] => Array(
        [:rb1] => 1
        [:im2] => 2
      )
    )

    ...

  )
)

I expected for compiled query string like this:

SELECT * FROM .. WHERE id IN(
    (SELECT id FROM .. WHERE .. ORDER BY .. LIMIT 1),
    (SELECT id FROM .. WHERE .. ORDER BY .. LIMIT 1)
) ORDER BY .. LIMIT 5

This is what I do in my code

$criteria = new CDbCriteria( ... );
$sql = $this->commandBuilder->createFindCommand($tableName, $criteria)->getText();
$queries[] = new CDbExpression($sql, $criteria->params);

Then I try to combine subqueries to one complex query

$criteria = new CDbCriteria( ... );
$criteria->addInCondition('id', $queries);

And finally, I try to get the result as SQL-query

$sql = $this->commandBuilder->createFindCommand($tableName, $criteria)->getText();

try reading here http://www.yiiframework.com/forum/index.php/topic/16154-custom-cdbconnection/page__p__81724#entry81724 — Muhammad Omer Aslam, Mar 04 '18 at 11:55

score 1 · Answer 1 · answered Oct 06 '20 at 05:31

You get the SQL with params, as you have

$sql = $this->commandBuilder->createFindCommand($tableName, $criteria)->getText();

Then get the params enclosed in quotes:

$params = array_map(function($param) { return '"' . $param . '"'; }, $criteria->params);

Finally, replace the pairs:

echo strtr($sql, $params);

score 0 · Answer 2 · answered Feb 15 '18 at 00:53

You can use enableParamLogging in your db config (boolean). You can set it up so it's used conditionally -- make sure to not use it in production.

    'db' => [
        'connectionString' => 'mysql:host=' . $dbhost . ';port=' . $dbport . ';dbname=' . $dbname,
        'emulatePrepare' => true,
        'username' => $db_user,
        'password' => $db_pass,
        'schemaCachingDuration' => 3600,
        'charset' => 'utf8',
        'enableProfiling' => $db_profile,
        'enableParamLogging' => $db_params,
    ],

Then you can add 'class'=>'CWebLogRoute' to your log routes if you want to see all the output in your browser. docs

Thank you, but I need to use it in production code. Actually, I didn't know how the Yii Framework creates queries. So I needed "createInCondition" method. https://github.com/yukal/Tutvse-Yii1/blob/master/protected/models/News.php#L732 And this is what I have done: https://github.com/yukal/Tutvse-Yii1/blob/master/protected/models/News.php#L647 — Alexander Yukal, Feb 18 '18 at 10:47

score 0 · Answer 3 · answered Aug 13 '19 at 15:06

0

I think you want to do something like this: $criteria = ...; $command = $builder->createFindCommand($schema->getTable('name_of_table'), $criteria); $results = $command->text;

answered Aug 13 '19 at 15:06

ermSO

325
1
2
12

score -1 · Answer 4 · answered Feb 14 '18 at 19:19

-1

There are few other options to perform the same thing but i will suggest you should use the following

$results = MyModel::model()->findAllBySql("...");

Or you can prefer the following : Sub-queries ActiveRecord Yii

answered Feb 14 '18 at 19:19

Ketan Vekariya

334
1
11

Well, I don't want to use this method because it needs to write much SQL text in the model, and it looks terrible. I like to use PDO. – Alexander Yukal Feb 14 '18 at 21:16

Getting a raw SQL query with variables in Yii1

4 Answers4