pg库中数组形式字段浅显使用

薄洪涛5年前数据库1336

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

image.png

image.png

但是问题来了,比如我想要搜索数组中的数据,比如搜索所有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为例,通常一个管理员会对应一个角色,那么我们可以设计一个管理员对应多个角色的模式;那么在数据库中的管理员的角色字段应该是个数组类型,那么在界面上我们直接可以设计复选框

image.png

然后怎么办呢?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,
        ]);
    }
}
标签: postgre

相关文章

redis查找附近的人

Redis GEO 主要用于存储地理位置信息,并对存储的信息进行操作,该功能在 Redis 3.2 版本新增。Redis GEO 操作方法有:geoadd:添加地理位置的坐标。geopos:获取地理位...

redis中HyperLogLog基数统计

简介Redis 在 2.8.9 版本添加了 HyperLogLog 结构。Redis HyperLogLog 是用来做基数统计的算法,HyperLogLog 的优点是,在输入元素的数量或者体积非常非常...

CREATE TABLE 表名 AS SELECT 语句用法详解

新表不存在,创建新表1.创建新表并且携带数据create table new_table    as select *&...

mysql几种常见的连接方式

mysql几种常见的连接方式

内连接(inner join)从两张表中取出所有记录做笛卡尔积;利用匹配条件进行匹配;如果内连接没有条件,那么其实就是交叉连接,这是我们应该避免的交叉连接从两张表中取出所有记录做笛卡尔积,所有的结果都...

摘抄--别在MySQL中使用UTF-8

摘抄--别在MySQL中使用UTF-8

直接看复现过程insert into user (id,name,sex) values (1,'机智如我','男')[Err] 1366 -&nb...

GROUP_CONCAT用法

GROUP_CONCAT用法

应用场景表A有一条数据,表B有3条数据,通过  A left join B 可以展示出3条数据,如图但是我想让name在一行显示SELECT A.id, GROUP_CONCAT(B.n...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。