pg库中数组形式字段浅显使用
我们在最近的项目中,数据表中某些字段类型是数组,举个例子
但是问题来了,比如我想要搜索数组中的数据,比如搜索所有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 fromyii\validators\IpValidator
toyii\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 throwingyii\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 implementsArrayAccess
,Traversable
andCountable
interfaces) and expects array oryii\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 usenew 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 inindexBy
property anymore.
In case you override default ActiveQuery implementation and relied on that behavior, callyii\db\Query::populate()
method instead to index query results according to theindexBy
parameter.Log targets (like
yii\log\EmailTarget
) are now throwingyii\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
withyii\base\InvalidArgumentException
.Replace calls to
Yii::trace()
withYii::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
tofalse
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, ]); } }