Performing raw SQL queries in Yii2?
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.
Comments
-
Lenny Carmi almost 2 years
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 thatbalance_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 ");
-
Lenny Carmi about 9 yearsThank 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? -
Jap Mul about 9 yearsThe error was because of the namespace. If you want to use
Yii
without the slash you have to adduse Yii;
at the top of your class. -
David J Eddy about 8 yearsAs a best practice I make it a habit of doing
\Yii::$app...
anywhere I use the Yii object inline.