常用方法:
TableClass::find() ->返回yii\db\ActiveQuery Object
->select(string|array) ->要查询的字段,如['id','name','age']
->where($condition(array|string), $params = []) ->查询条件
->asArray() ->返回数组
->groupBy(string|array) ->分组eg:"id, name" 或 ["id, name")]
->distinct( true) ->是否去重
->limit(int) ->限制返回结果条数 eg:limit(3) 结果:limit 3
->offset(int) ->跳过 x 条数据 eg:->offset(3) 结果:offset 3
->orderBy(string|array) ->排序规则eg:id ASC, name DESC
或 ['id' => SORT_ASC, 'name' => SORT_DESC]
->scalar() ->查询一个字段值,返回string
->one() ->查询一条,返回array
->all() ->查询所有,返回array
->count() ->查询结果的条数,返回string|null|false|0
->sum(string); ->求和,eg:->sum("(local_num+taobao_num)");
1.查询单条数据,返回
类实例
:
ProductMovie::findOne(['id'=>'10']); //返回ProductMovie Object
ProductMovie::findOne(10); //默认where条件是 主键=>10
同
ProductMovie::find()->where(['id'=>'10'])->one(); //返回ProductMovie Object
2.查询数据,返回
数组
:
//asArray是yii\db\ActiveQueryTrait中的方法
//需要在DicineProductRecord 中 use yii\db\ActiveQueryTrait
Product::find()->where(['id'=>'1'])->asArray()->one(); //返回一维数组,单条数据
Product::find()->where(['id'=>'1'])->asArray()->all(); //返回二维数组,多条数据
3.查询
单个字段值
$res = DicineProductRecord::find()->select(['name'])->where(['id'=>8])->scalar();
$res返回值:string 蛋糕99
4.sql练习:
//use yii\db\ActiveQueryTrait
$query = Product::find();
$res = $query->where
([
'OR',
['AND','is_valid =1','id<3'],
['AND','is_deleted =0','name="蛋糕99"'],
['between','id',9,11],
])
->andFilterWhere([ 'like', 'product_name', $nameVal, false, ]) // andFilterWhere(array) 如果你不确定$nameVal是否有值,可以这样写,如果$nameVal 没值,函数不会将字段product_name查询条件添加到sql语句的where条件中,这在筛选查询时非常有用
->asArray()
->select(['name'])
->limit(10)
->offset(1)
->orderBy('id DESC')
->distinct()
->all();
echo $query->createCommand()->getRawSql(); //打印sql语句
生成的sql:
SELECT DISTINCT `name` FROM `db_product` WHERE ((is_valid =1) AND (id<3)) OR ((is_deleted =0) AND (name="蛋糕99")) OR (`id` BETWEEN 9 AND 11) ORDER BY `id` DESC LIMIT 10 OFFSET 1
按
固定顺序排序
$query = Product::find();
$res =$query->where(
['id'=>[8,4,5,1,7]
])->asArray()
->orderBy(new Expression("FIELD(id,8,4,5,1,7)")) //按照id 8,4,5,1,7排序,field(str,str1,str2,str3)是mysql自定义的函数,SELECT FIELD("c", "a", "b", "c", "d", "e"); 返回字符串 c 在列表值中的位置->3
->select(['id','name'])
->limit(10)
->all();
//type=3的排在最前边,type=1的排在第二,同时按id倒序
->orderBy(new Expression("case when type=3 then 1 when type=1 then 2 else 3 end,id desc"))
以下我们介绍where()方法当中,条件的拼装方式。
#某个值为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表示自定义like条件,加上false后,程序不会在数组的第三个参数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)
5.插入数据:
插入多条数据:
\Yii::$app->db->createCommand()->batchInsert(Product::tableName(), ['name', 'age'], [
['Tom', 30],
['Jane', 20],
['Linda', 25],
])->execute();
插入一条:
$product = new Product();
$product->setAttributes($common);
$product->save();
6.更新数据表:
1.更新数值:在原来基础上增加+或减少-
ActiveRecord::updateAllCounters($counters, $condition = '', $params = []);
//updateAllCounters更新符合 $condition条件的所有数据
$res =Product::updateAllCounters(['sale_num' => -3],['>','id' , 743]);
//updateCounters只更新一条
$res =Product::findOne(['id' => 747])->updateCounters(['sale_num' => -3]);
或
$res = Product::find()->where(['id' => '747'])->one()->updateCounters(['sale_num' => -3]);
2.更新其他:
第一种:
$model = Product::findOne(['id' => 8]);
$model->setAttributes(['name' => '蛋糕']);
$res = $model->save();
第二种:function updateAll($attributes, $condition = '', $params = []){...}
ProductRecord::updateAll(
['name'=>'花雕1'],
['<','id','3']
);
7.删除数据:
//物理删除
Product::findOne(['id' => 8])->delete();
8.with关联查询:
在数据表类ProductClass中设置好关联关系,比如关联关系为category
在ProductClass中定义方法getCategory()方法
eg:
public function getCategory()
{
return $this->hasOne(ProductCategory::class, [
'id' => 'category_id',
])->select(['id','name']);
}
查询:
$condition = 0;
$query = Product::find();
$res = $query
->select('category_id')
->where(['<','id','6' ])
->with([
'category' => function ($query) use($condition) {
$query->where(['=','is_deleted',$condition]);
}
]) //关联表设置条件is_deleted = 0
->asArray()
->all();
或 ->with( 'category' )->with('supply') //关联表不设置查询条件,需定义getSupply方法
或 ->with([
'category' => function ($query) {
$query->where(['=','is_deleted',0]);
}
])
返回结果:
Array(
[0] => Array
(
[category_id] => 1
[category] =>
)
[1] => Array
(
[category_id] => 4
[category] => Array
(
[id] => 4
[name] => 食品
)
)
)