Menu
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 "); 
441
Also you need to execute the query. $query = (new \yii\db\Query())->select(['title'])->from('topics')->where(['id' => [1, 2, 3]]); $command = $query->createCommand(); $data = $command->queryAll(); $titles = ''; foreach($data as $row) { $titles . = $row['title'] . ', '; } return rtrim($titles, ', ');
Using Query Builder, you can search and filter database objects, select objects and columns, create relationships between objects, view formatted query results, and save queries with little or no SQL knowledge.
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.
159
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
