Support relations via array type columns

[Tigrov]

Currently AR supports scalar to array relations:

$this->hasMany(Item::class, ['id' => 'item_ids'])

where id is integer column
and item_ids is integer array column

But AR does not support array to scalar relations
$this->hasMany(Promotion::class, ['item_ids' => 'id'])

The reason is in preparing the query condition. Currently it is only IN condition

active-record/src/ActiveRelationTrait.php

Line 636 in b000730

$this->andWhere(['in', $attributes, $values]);

Which will generate condition like id IN (1, 2, 3)

But for array column this should be arrays overlap condition like item_ids && ARRAY[1, 2, 3] (Postgres)

To solve the issue it requires:

1. Realize ArrayOverlapCondition and JsonOverlapCondition (for json arrays) in db packages
2. Add AR::columnType($columnName) method to get type of the column
3. Prepare condition according to the column type:

match ($columnType) {
    'array' => $this->andWhere(new ArrayOverlapCondition($attributes, $values)),
    'json' => $this->andWhere(new JsonOverlapCondition($attributes, $values)),
    default => $this->andWhere(['in', $attributes, $values]),
};

[samdark]

What's the support in other DBs?

[Tigrov]

What's the support in other DBs?

• Pgsql, Mssql, Oracle support arrays but db package has realization only for Pgsql;
• Mysql, Pgsql, Sqlite, Mssql, Oracle support json but db package has realization only for Mysql, Pgsql, Sqlite.
  But it may not be possible to implement JsonOverlapCondition for all DBMSs. At least for Mysql and Pgsql it is easy to implement.

[samdark]

If it's MySQL and PostgreSQL then it is worth doing.

[Tigrov]

Done with #375