虽然 Yii DAO 可以处理几乎任何数据库相关的任务, 但很可能我们会花费 90% 的时间以编写一些执行普
CRUD(create, read, update 和 delete)操作的 SQL 语句。 而且我们的代码中混杂了SQL语句时也会变得难以维护。
要解决这些问题,我们可以使用 Active Record。
1、增
#1.save增
$model = new TestProduct();
$model->username = '模型2';
$model->sex = 2;
$model->add_time = date('Y-m-d H:i:s');
$model->update_time = new CDbExpression('NOW()'); #其属性可以赋值为 CDbExpression 类型,保存一个由 MySQL 的 NOW() 函数返回的时间戳
$data = $model->save();
$self = new self();
$self->setAttributes($data,false);
$self->save();
return $self->getPrimaryKey();
#2.insert增
$self->getDbConnection()
->createCommand()
->insert(self::tableName(), $data);
2、查询一行数据
#2.查询一行数据
#2.1:find($condition,$params);
#查找满足指定条件的结果中的第一行
$data = TestProduct::model()->find();
#2.2:查找具有指定主键值的那一行
#findByPk($postID,$condition,$params);
$data = TestProduct::model()->findByPk(2);
#2.3:查找具有指定属性值的行,一行
#findByAttributes($attributes,$condition,$params);
$data = TestProduct::model()->findByAttributes('username,sex','id = :id',[':id'=>6]);
#2.4:通过指定的 SQL 语句查找结果中的第一行
#findBySql($sql,$params);
$sql = "select * from test where id = :id";
$data = TestProduct::model()->findBySql($sql,[':id'=>5]);
3、查询所有行数据
#3.查询所有行数据
#3.1:findAll($condition,$params);
#查找满足指定条件的所有行
$data = TestProduct::model()->findAll();
#3.2:查找带有指定主键的所有行
#findAllByPk($postIDs,$condition,$params);
$data = TestProduct::model()->findAllByPk([5,6]);
#3.3:查找带有指定属性值的所有行
#findAllByAttributes($attributes,$condition,$params);
$data = TestProduct::model()->findAllByAttributes('username,sex');
#4.4:通过指定的SQL语句查找所有行
#findAllBySql($sql,$params);
$sql = "select * from test where id = :id";
$data = TestProduct::model()->findAllBySql($sql,[':id'=>5]);
4、快捷查询
#4.快捷查询
#4.1:获取满足指定条件的行数
#count($condition,$params);
$data = TestProduct::model()->count();
#4.2:通过指定的 SQL 获取结果行数
#countBySql($sql,$params);
$sql = "select * from test";
$data = TestProduct::model()->countBySql($sql); #错误?
#4.3:检查是否至少有一行复合指定的条件
#exists($condition,$params);
#存在返回bool(true),没有返回bool(false)
$data = TestProduct::model()->exists('sex=:sex',[':sex'=>2]);
5、对比查询
$shippingArr = Ebayonlinelistingshipping::model()->findAllByAttributes(
'item_id,shipping_service_cost',
'item_id in ('.$itemidStrs.') and shipping_status=:s and shipping_service_priority=:p',
[':s'=>1,':p'=>'1']);
$shippingObj = Ebayonlinelistingshipping::model()->findAll(array(
'select' => 'item_id,shipping_service_cost',
'condition' => 'item_id in ('.$itemidStrs.') and shipping_status=:s and shipping_service_priority=:p',
'params' => [':s'=>1,':p'=>'1']
));
#对象转数组
$shippingArr = json_decode(CJSON::encode($shippingArr),TRUE);
$shippingArr = Ebayonlinelistingshipping::model()->dbConnection->createCommand()
->select('item_id,shipping_service_cost')
->from(Ebayonlinelistingshipping::staticModel()->tableName())
->where('item_id in ('.$itemidStrs.') and shipping_status=:s and shipping_service_priority=:p',[':s'=>1,':p'=>'1'])
->queryAll();
6、更新
#1、update需要先查询
$model = YbModel::model('Cdiscountskubind')->findByPk($id);
if ($model){
$model->is_deleted = 1;
$model->update(['is_deleted']);
}
#2、其中$id需为主键,可以传递单个或数组
$model->updateByPk($id,['is_deleted'=>1])
7、查询 or
$where = [];
if (isset($data['item_id']) && !empty($data['item_id'])){
$where[] = 'item_id = "'.$data['item_id'].'"';
}
if (isset($data['listing_id']) && isset($data['type'])){
$where[] = 'listing_id = '.$data['listing_id'].' and type = "'.$data['type'].'"';
}
if (empty($where)) return ['error' => 400 , 'msg' => '参数不合法'];
$criteria = new CDbCriteria;
$criteria->select='*';
$criteria->addCondition($where,'or');
$models = $this->findAll($criteria);
select * from tabel where (item_id = "112635103043") or (listing_id = 31 and type = "migrate")