Performing raw SQL queries in Yii2?

Question

I have written the below queries as I migrate my PHP website to the Yii2 framework. I want to add them to my controller so as to display the top 10 bets won. I have tried going through many Yii2 database classes but I cannot get it to work.

My tables are:

users:

id | user_name | user_status | ...other columns...

bets:

id | user_id | date_time |...other columns...| balance_return

The queries I want to get in Yii2 are:

$query_all = $dbh->query("
    SELECT SUM(bets.balance_return) AS total_win
         , bets.user_id
         , users.user_name
         , users.user_status
      FROM bets INNER JOIN users ON bets.user_id = users.id
     WHERE users.user_status = 'verified'
       AND bets.date_time > " . $start_date . "
  GROUP BY bets.user_id
  ORDER BY total_win DESC
");

The variable start_date is a period of 6 months which I calculate according to time() Also please note that balance_return is every win a user got so its sum determines the ranking.

The second query is:

$qwi = $dbh->query("
    SELECT SUM(bets.balance_return) AS total_win
         , bets.user_id
         , users.user_name
         , users.user_status
      FROM bets INNER JOIN users ON bets.user_id = users.id
     WHERE users.user_status = 'verified'
       AND bets.date_time > " . $start_date . "
  GROUP BY bets.user_id
  ORDER BY total_win DESC LIMIT 0,10
");

Visibility

Answer 1

You can execute raw sql like this

$connection = Yii::$app->getDb();
$command = $connection->createCommand("
    SELECT SUM(bets.balance_return) AS total_win
     , bets.user_id
     , users.user_name
     , users.user_status
    FROM bets INNER JOIN users ON bets.user_id = users.id
    WHERE users.user_status = 'verified'
    AND bets.date_time > :start_date
    GROUP BY bets.user_id
    ORDER BY total_win DESC", [':start_date' => '1970-01-01']);

$result = $command->queryAll();

I recommend reading: http://www.yiiframework.com/doc-2.0/yii-db-connection.html#createCommand()-detail

The first parameter is the sql (with placeholder(s)) and the second part is an array of values to be used with the placeholders.

Barefaced

Thank you so much for answering the question. I managed to get it to work after getting stuck for two days. Also I wanted to note that when I call $connection = Yii::$app->getDb(); from a module controller I receive an error, I had to call $connection = \Yii::$app->getDb(); Why would this be? – Visibility

The error was because of the namespace. If you want to use Yii without the slash you have to add use Yii; at the top of your class. – Barefaced

As a best practice I make it a habit of doing \Yii::$app... anywhere I use the Yii object inline. – Figureground

Answer 2

if $sql is your raw query for execute raw select query

$result = \Yii::$app->getDb()->createCommand($sql)->queryAll();

for execute raw insert/delete/update query

$result = \Yii::$app->getDb()->createCommand($sql)->execute();

Anemometry