php - yii2如何编写sql查询
问题描述
我有一个使用 yii2 运行的原始 sql 查询,Yii::$app->db->createCommand()
但后来我想知道如何使用 yii 编写查询的方法来实现同样的事情。
$m = "SELECT p.basic_plan_amt AS basic,p.premium_plan_amt AS premium,p.daju_plan_amt AS daju,c.name
AS country FROM price p LEFT JOIN country c ON p.country_id = c.id WHERE p.country_id = " .$country_id;
虽然上面的查询运行良好并提供了预期的结果,但是我怎样才能使用下面的格式编写相同的查询
$model = \backend\models\Price::find()->select([p.basic_plan_amt AS basic,p.premium_plan_amt AS
premium,p.daju_plan_amt AS daju,c.name
AS country])->where(['country_id' => $country_id])->all();
解决方案
下面是yii中的查询示例,希望对您有所帮助更多参考
关系模型
$model = User::find()
->with('comments')
->all();
foreach ($model as $user) {
// get data from relation model
$comments = $user->comments;
......
foreach($comments as $comment){
........
}
}
joinWith() 示例 1:
$model = User::find()
->joinWith('comments')
->all();
样本 2:
$model = User::find()
->joinWith('comments')
->orderBy('tbl_comments_id.id, tbl_user.id')
->all();
内部连接()
$model = User::find()
->innerJoinWith('comments', false)
->all();
// equivalent to the above
$model = User::find()
->joinWith('comments', false, 'INNER JOIN')
->all();
加入()
JOIN_TYPE = INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN etc
Syntax
$query = new Query;
$query ->select(['SELECT COLUMNS'])
->from('TABLE_NAME_1')
->join( 'JOIN_TYPE',
'TABLE_NAME_2',
'TABLE_NAME_2.COLUMN =TABLE_NAME_1.COLUMN'
);
$command = $query->createCommand();
$data = $command->queryAll();
样品 1:
$query = new Query;
$query ->select([
'tbl_user.username AS name',
'tbl_category.categoryname as Category',
'tbl_document.documentname']
)
->from('tbl_user')
->join('LEFT OUTER JOIN', 'tbl_category',
'tbl_category.createdby =tbl_user.userid')
->join('LEFT OUTER JOIN', 'tbl_document',
'tbl_category.cid =tbl_document.did')
->LIMIT(5) ;
$command = $query->createCommand();
$data = $command->queryAll();
输出查询
SELECT `tbl_user`.`username` AS `name`, `tbl_category`.`categoryname` AS `Category`
FROM `tbl_user`
LEFT OUTER JOIN `tbl_category` ON tbl_category.createdby =tbl_user.userid
LEFT OUTER JOIN `tbl_document` ON tbl_category.cid =tbl_document.did
LIMIT 5
leftJoin() 示例 1:
$query = new Query;
$query ->select(['tbl_user.username AS name', 'tbl_category.type as Category'])
->from('tbl_user')
->leftJoin('tbl_category', 'tbl_category.createdby = tbl_user.userid')
->limit(2);
$command = $query->createCommand();
$data = $command->queryAll();
输出查询
SELECT `tbl_user`.`username` AS `name`, `tbl_category`.`type` AS `Category`
FROM `tbl_user`
LEFT JOIN `tbl_category` ON tbl_category.createdby = tbl_user.useridd
LIMIT 2
推荐阅读
- jquery - 提交位于主表单内的模态表单
- compilation - 无效的左侧赋值是编译错误还是编译中的评估错误
- javascript - 在 Java 应用程序中生成 JavaScript 代码
- blockchain - 如何计算区块链交易费用
- reactjs - Big React 日历年视图
- google-api - 用于 sheet.googleapis.com 的非阻塞套接字的 SSL_connect
- amazon-dynamodb - 从 flink 集群外部访问 flink 状态的方法有哪些?
- angular - 材质组件中的元素引用仅返回 _animationMode 属性
- c++ - ctor vs connect方法中的boost socket vs stream_socket端点
- javascript - Vuejs ajax call not mapping changes to underlying html table