CODEWITHSUNDEEP
arrayspostgresqlmodelyii
Anand Sainath
Anand Sainath

Reputation: 1807

Inserting an array column into the db in Yii

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!

Upvotes: 2

Views: 4713

Answers (2)

Koss Petroff
Koss Petroff

Reputation: 41

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.

Upvotes: 3

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656784

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.

Upvotes: 3

Related Questions