php 在 Yii2 中执行原始 SQL 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30081660/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Performing raw SQL queries in Yii2?
提问by Lenny Carmi
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_returnis every win a user got so its sum determines the ranking.
变量 start_date 是我计算的 6 个月的时间段time()另请注意，这balance_return是用户获得的每一次胜利，因此其总和决定了排名。
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
");
回答by Jap Mul
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()-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.
第一个参数是 sql（带有占位符），第二部分是与占位符一起使用的值数组。
