转自:https://blog.csdn.net/u011341352/article/details/78226024
熟悉Yii2的查询条件后,用Active Record查询数据非常方便。
以下我们介绍where()方法当中,条件的拼装方式。
数组:
model::find()->where(['type' => 1, 'status' => 2] )->asArray()->all()
#某个值为null,会用IS NULL来生成语句:
['type' => 1, 'status' => 2] // 生成:(type = 1) AND (status = 2)
['id' => [1, 2, 3], 'status' => 2] // 生成:(id IN (1, 2, 3)) AND (status = 2)
['status' => null] // 生成:status IS NULL
['NOT', ['type' => null]] // 生成:type IS NOT NULL
#对比
['>', 'id', 1] // 生成:id > 1
['<', 'id', 100] // 生成:id < 100
['=', 'id', 10] // 生成:id = 10
['>=', 'id', 1] // 生成:id >= 1
['<=', 'id', 100] // 生成:id <= 100
['!=', 'id', 10] // 生成:id != 10
['and', 'id' => 1, 'id' => 2] // 生成:id=1 AND id=2
['and', 'id=1', 'id=2'] // 生成:id=1 AND id=2
['and', 'type=1', ['or', 'id=1', 'id=2']] // 生成:type=1 AND (id=1 OR id=2)
['or', ['type' => [7, 8, 9]], ['id' => [1, 2, 3]]] // 生成:(type IN (7, 8, 9) OR (id IN (1, 2, 3)))
['not', ['attribute' => null]] // 生成:NOT (attribute IS NULL)
['between', 'id', 1, 10] // 生成:id BETWEEN 1 AND 10
['not between', 'id', 1, 10] // 生成:id NOT BETWEEN 1 AND 10
['in', 'id', [1, 2, 3]] // 生成:id IN (1, 2, 3)
['id' => [4, 8, 15]] // 生成:id IN (4, 8, 15)
['not in', 'id', [1, 2, 3]] // 生成:id NOT IN (1, 2, 3)
['in', ['id', 'name'], [['id' => 1, 'name' => 'foo'], ['id' => 2, 'name' => 'bar']]] // 生成:(`id`, `name`) IN ((1, 'foo'), (2, 'bar'))
#用子查询作为IN条件的值,如下:
['in', 'user_id', (new Query())->select('id')->from('users')->where(['active' => 1])]
['like', 'name', 'tester'] // 生成:name LIKE '%tester%'
['like', 'name', ['test', 'sample']] // 生成:name LIKE '%test%' AND name LIKE '%sample%'
['like', 'name', '%tester', false] // 生成:name LIKE '%tester'
// 这是自定义查询方式,要传入值为false的运算数3,并且自行添加%
['or like', 'name', ['test', 'sample']] // 生成:name LIKE '%test%' OR name LIKE '%sample%'
['not like', 'name', 'tester'] // 生成:name NOT LIKE '%tester%'
['or not like', 'name', ['test', 'sample']] // 生成:name NOT LIKE '%test%' OR name NOT LIKE '%sample%'
['exists', (new Query())->select('id')->from('users')->where(['active' => 1])] // 生成:EXISTS (SELECT "id" FROM "users" WHERE "active"=1)
字符串格式,例如:'status=1'
哈希格式,例如: ['status' => 1, 'type' => 2]
操作符格式,例如:['like', 'name', 'test']
$status = 10;
$search = 'yii';
$query->where(['status' => $status]);
if (!empty($search)) {
$query->andWhere(['like', 'title', $search]);
}
生成的语句就是
... WHERE (`status` = 10) AND (`title` LIKE '%yii%')
第一种最简单的就是上面提到的例子
andWhere(['like', 'title','搜索的标题']);
生成的语句
... WHERE (`status` = 10) AND (`title` LIKE '%yii%')
第二种
addWhere(['and', 'id=1', 'name=2']);
生成的语句
... WHERE id=1 AND name=2
第三种
addWhere(['and', 'type=1', ['or', 'id=1', 'id=2']]);
生成的语句
... WHERE type=1 AND (id=1 OR id=2);
第四种
->andWhere(['or like','name',['哈哈','苦苦']]);
生成的语句
WHERE `name` LIKE '%哈哈%' OR `name` LIKE '%苦苦%';
第五种
addWhere(['or',['like','name','哈哈'],['like','title','苦苦']]);//操作符格式的嵌套
生成的语句
... WHERE (`status`=1) AND ((`name` LIKE '%哈哈%') OR (`title` LIKE '%苦苦%'))
————————————————
原文链接:https://blog.csdn.net/u011341352/article/details/78226024
其他:
-
你使用关联查询的时候, 你想排除掉副表不满足的条件下, 主表也给排除掉, 那么我们这时候就选 JoinWith否则使用with
-
带with条件(例如model中有getComment方法和getPostDetail):
Post::find()->where(['type' => 1, 'status' => 2] )->with('comment', 'postDetail')->asArray()->all();
应用场景举例:
需要查找所有用户的信息,并关联查询(with)出用户的发表过的文章,且文章的评论不为0的数据。
$user= user::find()->with([
'articles' => function ($query){
$query->where('comment>0');
}
])->all();
$query是YII返回的查询类,同ActiveQuery一样,可以对其使用model的相关命令,如:
$query->where('comment>0')->orderby('update_time DESC')
也可以使用闭包函数进行传递外部参数
var $condition = 0;//评论数对比条件
$user= user::find()->with([
'articles' => function ($query) use($condition) {
$query->where(['>','comment',$condition]);
}
])->all();
网友评论