在Yii2中执行原始SQL查询?

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.

在将PHP网站迁移到Yii2框架时，我编写了以下查询。我想把它们添加到我的控制器中，以便显示前10个赢的赌注。我已经尝试过许多Yii2数据库类，但是我无法让它正常工作。

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:

我想在Yii2中得到的查询是:

$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.

变量start_date是一个6个月的周期，我根据time()计算它，还请注意balance_return是一个用户获得的每一个win，因此它的总和决定了排名。

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
");

1 个解决方案

#1

You can execute raw sql like this

可以像这样执行原始sql

$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

我推荐阅读:http://www.yiiframework.com/doc - 2.0 / - yii - db - connection.html # createCommand()的细节

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

第一个参数是sql(带有占位符)，第二部分是与占位符一起使用的值数组。

#1