53

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
");
timclutton
  • 12,682
  • 3
  • 33
  • 43
Lenny Carmi
  • 763
  • 1
  • 6
  • 12

2 Answers2

112

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.

Jap Mul
  • 17,398
  • 5
  • 55
  • 66
  • 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? – Lenny Carmi May 08 '15 at 09:45
  • 7
    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. – Jap Mul May 09 '15 at 15:33
  • As a best practice I make it a habit of doing ```\Yii::$app...``` anywhere I use the Yii object inline. – David J Eddy May 18 '16 at 21:34
1

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();
Mohammad Hajibegloo
  • 11
  • 3