Yii框架下的SQL技巧

Expression()函数

查找 categories字段中包含有3的数据

categories字段保存格式为 1,2,3 ，用逗号连接的id

yii 如何 实现 where find_in_set(3, categories)

# categories 为字段名

Categories::find()->where(new Expression('FIND_IN_SET(:category_to_find, categories)'))->addParams([':category_to_find' => 3])->asArray()->all(); 

模型中事务编写

Yii::$app->db->transaction(function() {
    $order = new Order($customer);
    $order->save();
    $order->addItems($items);
});

执行SQL查询并缓存结果

调用yii\db\Connection的cache方法，写入回调函数执行SQL查询并缓存结果。

$id = Yii::$app->request->get('id');
$collection = Yii::$app->db->cache(function (Connection $db) use($id){
	return self::findOne(['id'=>$id]);
},10); // 缓存10秒
var_dump($collection);

嵌套查询

$subQuery = new Query();
$subQuery->from(PostComment::tableName())->where(['status' => PostComment::STATUS_ACTIVE])->orderBy(['created_at' => SORT_DESC]);
$comment = PostComment::find()->from(['tmpA' => $subQuery])
    ->groupBy('post_id')
    ->all();

生成如下语句：SELECT * FROM (SELECT * FROMpost_commentWHEREstatus=1 ORDER BYcreated_atDESC)tmpAGROUP BYpost_id``

批量写入数据

下面是一个将excel数据导入到数据库的例子，使用batchInsert()方法进行数据的写入，提高效率。

 Yii::$app->db->createCommand()
->batchInsert('branches', ['branch_id','companies_company_id','branch_name','branch_address','branch_created_date','branch_status'], $data)
->execute();

exists

//查找发布过文章的用户
//select * from user exists (select author_id from post where user.id=post.author_id);

$query = new Query();
$query->from($userTable)
    ->where(['exists',
        (new Query())
            ->select('author_id')
            ->from($postTable)
            ->where([$userTable . '.id=' . $postTable . '.author_id'])
    ]);

“外层查询表小于子查询表，则用exists，外层查询表大于子查询表，则用in

in

//IN条件也适用于多字段  
$cond = ['in', ['id', 'name'], [['id' => 1, 'name' => 'foo'], ['id' => 2, 'name' => 'bar']]]  
  
//也适用于内嵌sql语句  
$cond = ['in', 'user_id', (new Query())->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]