InvalidConfigException using relation with array column in PostgreSQL

[oleg1540]

InvalidConfigException using relation with array column in PostgreSQL.

Value must be convertable to string.

What steps will reproduce the problem?

1. Create 2 tables.

CREATE TABLE IF NOT EXISTS public.order
    (
        order_id serial NOT NULL,
        item_ids integer[] NOT NULL,
        CONSTRAINT order_pkey PRIMARY KEY (order_id)
    );

CREATE TABLE IF NOT EXISTS public.item
    (
        item_id serial NOT NULL,
        CONSTRAINT item_pkey PRIMARY KEY (item_id)
    );

2. Create relation in Order model.

public function getItems(): ActiveQuery
{
    return $this->hasMany(Item::class, ['item_id' => 'item_ids']);
}

3. Try to use eager loading using with().

$orders= TestOrder::find()
    ->with('items')
    ->all();
var_dump($orders[0]->items);

4. Got InvalidConfigException.

What is the expected result?

Eager loading without exception.

What do you get instead?

Seems that problem wiil be resolved if allow work with ArrayExpression in ActiveRecordTrait.

Additional info

Q	A
Yii version	2.0.47
PHP version	8.1
Operating system	MacOS

[bizley]

Is item_ids an array? hasMany is for the relation fetching, you need to establish the FK first.

[oleg1540]

I don't know "out of the box" method to create FK on array in PostgreSQL.

If remove with() from query and disable eager loading, everything works fine, items are displayed. But with eager loading got an exception, it's a bit strange.

[bizley]

What do you mean? You add column in the item to point to the order and you add foreign key on it.

[oleg1540]

Ok, Order <-> Item is not the best example.

Table A is some dictionary. Table B is some entity, B 1->n A. Table C is some another entity, C 1->n A, etc. Of course, I can create link table D with columns A_id, B_id and FKs and use hasMany() with viaTable().

But the question, is it possible to do without a link table and use an array column B.A_ids? hasMany() allow to do this, but eager loading throw an exception, why?

[bizley]

I have never use it like that before, and it might be just a pure luck it works in that case (but not in every one as we can see) but you cannot expect it to work for use-case that it was not designed for.

[oleg1540]

I also was a little surprised when hasMany didn't throw exception in this case. But it works ) Then with eager loading caught exception convertable to string. ActiveRelationTrait::populateRelation support array. https://github.com/yiisoft/yii2/blob/1d557360d544e1fa871b2bd2f9ce7daf6d8b674d/framework/db/ActiveRelationTrait.php#L297 But in this case we have ArrayExpression. Why method support arrays and can not support ArrayExpression? Seems that if it was not designed for it then if (is_array($keys)) condition is redundant.

$keys is $primaryModel[$primaryModelKey] => property of model in link can be array. https://github.com/yiisoft/yii2/blob/1d557360d544e1fa871b2bd2f9ce7daf6d8b674d/framework/db/ActiveRelationTrait.php#L295

[bizley]

ArrayExpression is DB related construct, not a PHP thing. Anyway, @samdark , thoughts?

[samdark]

Well, with some additional tests I don't mind having it.

[PowerGamer1]

Table A is some dictionary. Table B is some entity, B 1->n A. Table C is some another entity, C 1->n A, etc. Of course, I can create link table D with columns A_id, B_id and FKs and use hasMany() with viaTable().

And that is exactly what you should do. When you use relational database you always want to have foreign keys to ensure relational data integrity between tables. For example, with the "order" and "item" tables in your original post without FKs the database won't provide you a guarantee that all values in "order"."item_ids" have corresponding records in "items" table.

I don't think supporting in the engine a use-case that goes against the concept of relational databases is a good idea. That is without taking into account that any such support added to with() will have to be also added to other places like joinWith() for example and also correctly handle the fact that arrays in PostgreSQL can be multidimensional.