activerecord活動記錄類
一.聲明ar類(模型層)
namespaceapp\models;
useyii\db\activerecord;
classcustomer extends activerecord
{
/**
*@return string 傳回該ar類關聯的資料表名
*/
publicstatic function tablename()
{
return 'customer';
}
}
輸出sql:createcommand()->getrawsql();
二.使用ar類進行增删改查(控制器層)
//"id" 和"mail" 是 $customer 對象所關聯的資料表的對應字段名
$id = $customer->id;
$email= $customer->email;
ar提供了兩種方法來建構 db 查詢并向 ar 執行個體裡填充資料:
yii\db\activerecord::find()
yii\db\activerecord::findbysql()
1.簡單查詢方法
customer::find()->one(); 此方法傳回一條資料;
customer::find()->all(); 此方法傳回所有資料;
customer::find()->count(); 此方法傳回記錄的數量;
customer::find()->average(); 此方法傳回指定列的平均值;
customer::find()->min(); 此方法傳回指定列的最小值;
customer::find()->max(); 此方法傳回指定列的最大值;
customer::find()->scalar(); 此方法傳回值的第一行第一列的查詢結果;
customer::find()->column(); 此方法傳回查詢結果中的第一列的值;
customer::find()->exists(); 此方法傳回一個值訓示是否包含查詢結果的資料行;
customer::find()->asarray()->one(); 以數組形式傳回一條資料;
customer::find()->asarray()->all(); 以數組形式傳回所有資料;
customer::find()->where($condition)->asarray()->one(); 根據條件以數組形式傳回一條資料;
customer::find()->where($condition)->asarray()->all(); 根據條件以數組形式傳回所有資料;
customer::find()->where($condition)->asarray()->orderby('iddesc')->all(); 根據條件以數組形式傳回所有資料,并根據id倒序;
2.findone()和findall()
// 查詢年齡為30,狀态值為1的客戶
$customer = customer::findone(['age' =>30, 'status' => 1]);
$customer =customer::find()->where(['age' => 30, 'status' => 1])->one();
// 查詢id值為10,11,12的客戶
$customers = customer::findall([10, 11,12]);
$customers =customer::find()->where(['id' => [10, 11, 12]])->all();
3.where()條件
$customers = customer::find()->where($cond)->all();
$cond寫法舉例:
// sql: (type = 1) and (status = 2).
$cond =['type' => 1, 'status' => 2]
// sql:(id in (1, 2, 3)) and (status = 2)
$cond =['id' => [1, 2, 3], 'status' => 2]
//sql:status is null
$cond = ['status' => null]
[[and]]:将不同的條件組合在一起,用法舉例:
//sql:`id=1 and id=2`
$cond = ['and', 'id=1', 'id=2']
//sql:`type=1 and (id=1 or id=2)`
$cond = ['and', 'type=1', ['or', 'id=1','id=2']]
[[or]]:
//sql:`(type in (7, 8, 9) or (id in (1, 2,3)))`
$cond = ['or', ['type' => [7, 8, 9]],['id' => [1, 2, 3]]
[[not]]:
//sql:`not (attribute is null)`
$cond = ['not', ['attribute' => null]]
[[between]]: not between 用法相同
//sql:`id between 1 and 10`
$cond = ['between', 'id', 1, 10]
[[in]]: not in 用法類似
//sql:`id in (1, 2, 3)`
$cond = ['in', 'id', [1, 2, 3]]
//in條件也适用于多字段
$cond = ['in', ['id', 'name'], [['id' =>1, 'name' => 'foo'], ['id' => 2, 'name' => 'bar']]]
//也适用于内嵌sql語句
$cond = ['in', 'user_id', (newquery())->select('id')->from('users')->where(['active' => 1])]
[[like]]:
//sql:`name like '%tester%'`
$cond = ['like', 'name', 'tester']
//sql:`name like '%test%' and name like'%sample%'`
$cond = ['like', 'name', ['test','sample']]
//sql:`name like '%tester'`
$cond = ['like', 'name', '%tester', false]
此外,您可以指定任意運算符如下
//sql:`id >= 10`
$cond = ['>=', 'id', 10]
//sql:`id != 10`
$cond = ['!=', 'id', 10]
3.增删改
// 插入新客戶的記錄
$customer = new customer();
$customer->name = 'james';
$customer->email = '[email protected]';
$customer->save(); // 等同于 $customer->insert();
// 更新現有客戶記錄
$customer = customer::findone($id);
$customer->save(); // 等同于 $customer->update();
// 删除已有客戶記錄
$customer->delete();
// 删除多個年齡大于20,性别為男(male)的客戶記錄
customer::deleteall('age > :age andgender = :gender', [':age' => 20, ':gender' => 'm']);
// 所有客戶的age(年齡)字段加1:
customer::updateallcounters(['age' =>1]);
4.兩表聯查
[[activerecord::hasone()]]:傳回對應關系的單條記錄
[[activerecord::hasmany()]]:傳回對應關系的多條記錄
//客戶表model:customermodel
//訂單表model:ordersmodel
//首先要建立表與表之間的關系
//在customermodel中添加與訂單的關系
模型層:
class customermodel extends\yii\db\activerecord
{
...
public function getorders()
{
//客戶和訂單是一對多的關系是以用hasmany
//此處ordersmodel在customermodel頂部别忘了加對應的命名空間
//id對應的是ordersmodel的id字段,order_id對應customermodel的order_id字段
return $this->hasmany(ordersmodel::classname(), ['id'=>'order_id']);
}
}
控制器層:
public function actionindex{
$customer= customer::find()->where(['name'=>'zhangsan'])->one();
$orders= $customer->getorders();
或者
$orders= $customer->orders; //
魔術方法自動補全getorders();
//關聯查詢的多次查詢
$customer= customer::find()->with('order')->all();
foreach($customeras $customer){
$orders = $customer ->orders;
}
視圖層向控制器層傳值
首先在所在的視圖層 use yii\helpers\url;使用
get方式