Inserting an array column into the db in Yii

Question

I needed to insert an array field into a database and I was pleased to notice that PostGreSQL had that functionality. But now I am not able to insert the data using the tables active record.

I have tried the below calls with no success

$active_record->array_column = $_array_of_values;

which gives me the exception

Exception Raised:CDbCommand failed to execute the SQL statement: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: array value must start with "{" or dimension information

I have also tried this using

foreach($_array_of_values as $value){
    $active_record->array_column[] = $value;
}

which tells me

Indirect modification of overloaded property FeatureRaw::$colors_names has no effect

Can anyone help me with this? Thanks!

Community · Accepted Answer · 2017-05-23 10:30:40Z

3

Data must be inserted in the form (text representation of an ARRAY):

INSERT INTO tbl (arr_col) VALUES ('{23,45}')

Or:

INSERT INTO tbl (arr_col) VALUES ('{foo,"bar, with comma"}')

So you need to enclose your array values in '{}' and separate them with comma ,. Use double quotes "" around text values that include a comma.

I listed more syntax variants to insert arrays in a related answer.

edited May 23, 2017 at 10:30

CommunityBot

11 silver badge

answered Jan 19, 2012 at 13:55

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Anand Sainath Over a year ago

So does that mean I cannot use active records for inserting an array? Sheesh.

Erwin Brandstetter Over a year ago

@AnandSainath: I don't know enough about Active Records. I would expect there is a way. I added a link to more PostgreSQL syntax variants.

Anand Sainath Over a year ago

Yea, I got what you meant. Just that I couldnt find a way to do it in Yii. So I had to use implode to peice together the data and assign it to the array column.

Koss Petroff · Accepted Answer · 2016-04-20 11:03:46Z

3

For those who also have same problem:

I didn't check the Yii1 behavior, but in Yii2 you simply can insert array as properly formed string as Erwin Brandstetter mentioned in his comment:

$activeRecord->arrayField = '{' . implode(',',$array_values) . '}';

Of course you need to make additional efforts when your $array_values has strings with commas, etc. And you still need to convert value back to array after you load ActiveRecord.

You can make these conversions in ActiveRecord's beforeSave() and afterLoad() and you will not need to convert values manually.

UPD. Recently I made a simple behavior for Yii2 to use array fields with ActiveRecord without manual field building: kossmoss/yii2-postgresql-array-field. It is more generalized way to solve the problem and I hope it will help. For those who use Yii1: you can investigate the package code and create your own solutuion compatible with your framework.

edited Apr 20, 2016 at 11:03

answered Jan 28, 2016 at 3:55

Koss Petroff

414 bronze badges

2 Comments

111 Over a year ago

Would this help with yii2 grid view filtering for postgresql array data types? Thanks

Koss Petroff Over a year ago

@glyph I'm so sorry for a late reply. Yes, I use gridview with array fields and filtering works well.

Collectives™ on Stack Overflow

Inserting an array column into the db in Yii

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related