码农日记

我们在最近的项目中，数据表中某些字段类型是数组，举个例子

但是问题来了，比如我想要搜索数组中的数据，比如搜索所有flags含有popforum_recommend_topics 的行，需要怎么写sql呢

揭晓答案

select * from p_portal_article where popforum_recommend_topics = ANY (flags)

继续探究下，在框架中，这些数组类型的字段如何去维护呢，最开始的时候，我是拼接字符串去实现的，后来经过翻查文档后发现，Yii2.0在后续更新中增加了对数组和json的支持，见下面文档

Upgrade from Yii 2.0.13

• Constants IPV6_ADDRESS_LENGTH, IPV4_ADDRESS_LENGTH were moved from yii\validators\IpValidator to yii\helpers\IpHelper. If your application relies on these constants, make sure to update your code to follow the changes.

• yii\base\Security::compareString() is now throwing yii\base\InvalidArgumentException in case non-strings are compared.

• yii\db\ExpressionInterface has been introduced to represent a wider range of SQL expressions. In case you check forinstanceof yii\db\Expression in your code, you might consider changing that to checking for the interface and use the newly introduced methods to retrieve the expression content.

• Added JSON support for PostgreSQL and MySQL as well as Arrays support for PostgreSQL in ActiveRecord layer. In case you already implemented such support yourself, please switch to Yii implementation.

  In case this change makes the upgrade process to Yii 2.0.14 too hard in your project, you can switch off the described behaviorThen you can take your time to change your code and then re-enable arrays or JSON support.

• For MySQL JSON and PgSQL JSON & JSONB columns Active Record will return decoded JSON (that can be either array or scalar) after data population and expects arrays or scalars to be assigned for further saving them into a database.

• For PgSQL Array columns Active Record will return yii\db\ArrayExpression object that acts as an array (it implements ArrayAccess, Traversable and Countable interfaces) and expects array or yii\db\ArrayExpression to be assigned for further saving it into the database.

• yii\db\PdoValue class has been introduced to replace a special syntax that was used to declare PDO parameter type when binding parameters to an SQL command, for example: ['value', \PDO::PARAM_STR]. You should use new PdoValue('value', \PDO::PARAM_STR) instead. Old syntax will be removed in Yii 2.1.

• yii\db\QueryBuilder::conditionBuilders property and method-based condition builders are no longer used. Class-based conditions and builders are introduced instead to provide more flexibility, extensibility and space to customization. In case you rely on that property or override any of default condition builders, follow the special guide articleto update your code.

• Protected method yii\db\ActiveQueryTrait::createModels() does not apply indexes as defined in indexBy property anymore.
  In case you override default ActiveQuery implementation and relied on that behavior, call yii\db\Query::populate()method instead to index query results according to the indexBy parameter.

• Log targets (like yii\log\EmailTarget) are now throwing yii\log\LogRuntimeException in case log can not be properly exported.

• You can start preparing your application for Yii 2.1 by doing the following:

• Replace ::className() calls with ::class (if you’re running PHP 5.5+).

• Replace usages of yii\base\InvalidParamException with yii\base\InvalidArgumentException.

• Replace calls to Yii::trace() with Yii::debug().

• Remove calls to yii\BaseYii::powered().

• If you are using XCache or Zend data cache, those are going away in 2.1 so you might want to start looking for an alternative.

• In case you aren't using CSRF cookies (REST APIs etc.) you should turn them off explicitly by setting\yii\web\Request::$enableCsrfCookie to false in your config file.

举了栗子

我们以RBAC为例，通常一个管理员会对应一个角色，那么我们可以设计一个管理员对应多个角色的模式；那么在数据库中的管理员的角色字段应该是个数组类型，那么在界面上我们直接可以设计复选框

然后怎么办呢？load啊，然后直接save就可以了，是不是很简单

$model->load(Yii::$app->request->post()) && $model->save()

是不是感觉很无脑，编辑的时候呢，直接查询出数据，把model分配到view，然后那个角色的复选框就自动给你勾上，真的太方便了

给大家分享下增加管理员代码

public function actionCreate()
{
    $model = new Staff();
    $model->status = Staff::STATUS_ACTIVE;
    $model->setScenario('create');
    if ($model->load(Yii::$app->request->post()) && $model->save()) {
        foreach ($model->role as $role) {
            Yii::$app->authDbManager->assign(Yii::$app->authDbManager->getRole($role), $model->id . Yii::$app->user->idParam);
        }
        $this->redirectIndex();
    } else {
        return $this->render('create', [
            'model' => $model,
        ]);
    }
}

编辑：

public function actionUpdate($id)
{
    $model = $this->findModel($id);
    $model->setScenario('update');

    if ($model->load(Yii::$app->request->post()) && $model->save()) {
        Yii::$app->authManager->revokeAll($id.Yii::$app->user->idParam);
        foreach ($model->role as $role) {
            Yii::$app->authManager->assign(Yii::$app->authManager->getRole($role), $model->id . Yii::$app->user->idParam);
        }
        $this->redirectIndex();
    } else {
        return $this->render('update', [
            'model' => $model,
        ]);
    }
}