Yii2: ActiveRecord relation link with 'or' operator

[Agrumas]

Currently condition could be only with 'and' operator. https://github.com/yiisoft/yii2/blob/master/framework/db/ActiveQuery.php#L577
Third parameter could solve it.
BaseActiveRecord::hasOne($class, $link, $linkOperator = 'AND')
BaseActiveRecord::hasMany($class, $link, $linkOperator = 'AND')
If this solution is good i could write a code for it.

[qiangxue]

We do not have plan to support this, because it's very unusual that the link conditions would be connected with or operator. If you have such a special need, you can specify onCondition().

[Agrumas]

@qiangxue sadly this does not work, because loading relation requires link to be array with elements ActiveRelationTrait.php#L407. Population also uses link ActiveRelationTrait.php#L241.
I believe there is no way to do it with current AR implementation.
My use case:

/**
 * @property integer $id
 * @property integer $team_first
 * @property integer $team_second
 */
class Match extends \yii\db\ActiveRecord
{
}

class Team extends \yii\db\ActiveRecord
{
    public function getMatches() 
    {
        return $this->hasMany(Match::className(), [])->onCondition('team_first = :team OR team_second = :team', [':team' => $this->id,]);
    } 
}

[cebe]

Does it work when returning an ActiveQuery instance without using hasMany() ?

[Agrumas]

Lazy loading works, because it doesn't use link.

    public function getMatches()
    {
        $query = Match::find();
        $query->where('team_first = :team OR team_second = :team', [':team' => $this->id,]);
        $query->link = ['team_two' => 'id',];
        $query->multiple = true;
        return $query;
    }

AR generates wrong query using eager loading:

SELECT * FROM `match` WHERE (team_first = NULL OR team_second = NULL) AND (`team_second`='1')

[qiangxue]

@cebe I don't think we can do much to this. Both model population and link/unlink will be broken, and it's difficult to fix them.

[cebe]

I see, thought it was possible to have simple ActiveQuery defined as a relation but it does not really make sense, yes.

[wh1tew0lf]

Hello,

Now I try to use code like this:

 return $this->hasMany(Match::className(), [])
            ->onCondition('team_first = :team OR team_second = :team', [':team' => $this->id,]);

But it produce SQL:

ON (0=1) AND (team_first = 1 OR team_second = 1)

Now I use hack:

 return $this->hasMany(Match::className(), ['team_first' => 'id'])
            ->onCondition('1=1) OR (team_second = :id', [':id' => $this->id]);

But is there correct way to use ON condition in active record?

Thanks

[lieszkol]

Just spent 90 minutes trying to figure this out until I found @wh1tew0lf 's hack, thank you!
And I disagree that this is unusual. Things like this make using AQ a pain.

[cebe]

A relation is defined based on a foreign key. If activerecord loads relation records it needs to assign them properly in case of eager loading. If we allow selecting records out of the restriction of the foreign key, there is no way to correctly assign records to each other. So this is simply not possible to implement in terms of a relation. If you need to select records using such conditions, you can define a getter that runs the query and returns the records directly. That would be the lazy loading approach.

[rob006]

    /**
     * @return ActiveQuery
     */
    public function getTransactions()
    {
        $query = Transaction::find()
            ->andWhere([
                'or',
                ['customer_id' => $this->id],
                ['trader_id' => $this->id],
            ]);
        $query->multiple = true;

        return $query;
    }

[lieszkol]

@cebe In my case table B has two foreign key columns to table A. One can argue that there should only be one...but alas there are two. It is true that with this setup one B record can belong to two A records. But...so what :-) There are many real life scenarios where this is true, and it is a shame that AR cannot handle it.

@rob006 Thank you for the example.

And thank you both for the super quick response!