CODEWITHSUNDEEP
phpyiiyii2
MAX POWER
MAX POWER

Reputation: 5448

Yii2 - hasMany relation with multiple columns

I have a table message_thread:

id
sender_id
recipient_id

I want to declare a relation in my User model that will fetch all message threads as follows:

SELECT *
FROM message_thread
WHERE sender_id = {user.id}
    OR recipent_id = {user.id}

I have tried the following:

public function getMessageThreads()
{
    return $this->hasMany(MessageThread::className(), ['sender_id' => 'id'])
        ->orWhere(['recipient_id' => 'id']);
}

But it generates an AND query. Does anyone know how to do this?

Upvotes: 4

Views: 5619

Answers (2)

rob006
rob006

Reputation: 22174

You cannot create regular relation in this way - Yii will not be able to map related records for eager loading, so it not supporting this. You can find some explanation int this answer and related issue on GitHub.

Depending on use case you may try two approach to get something similar:

1. Two regular relations and getter to simplify access

public function getSenderThreads() {
    return $this->hasMany(MessageThread::className(), ['sender_id' => 'id']);
}

public function getRecipientThreads() {
    return $this->hasMany(MessageThread::className(), ['recipient_id' => 'id']);
}

public function getMessageThreads() {
    return array_merge($this->senderThreads, $this->recipientThreads);
}

In this way you have two separate relations for sender and recipient threads, so you can use them directly with joins or eager loading. But you also have getter which will return result ofboth relations, so you can access all threads by $model->messageThreads.

2. Fake relation

public function getMessageThreads()
{
    $query = MessageThread::find()
        ->andWhere([
            'or',
            ['sender_id' => $this->id],
            ['recipient_id' => $this->id],
        ]);
    $query->multiple = true;

    return $query;
}

This is not real relation. You will not be able to use it with eager loading or for joins, but it will fetch all user threads in one query and you still will be able to use it as regular active record relation - $model->getMessageThreads() will return ActiveQuery and $model->messageThreads array of models.

Why orOnCondition() will not work

orOnCondition() and andOnCondition() are for additional ON conditions which will always be appended to base relation condition using AND. So if you have relation defined like this:

$this->hasMany(MessageThread::className(), ['sender_id' => 'id'])
    ->orOnCondition(['recipient_id' => new Expression('id')])
    ->orOnCondition(['shared' => 1]);

It will generate condition like this:

sender_id = id AND (recipent_id = id OR shared = 1)

As you can see conditions defined by orOnCondition() are separated from condition from relation defined in hasMany() and they're always joined using AND.

Upvotes: 4

Shringiraj Dewangan
Shringiraj Dewangan

Reputation: 784

For this query 

SELECT *
FROM message_thread
WHERE sender_id = {user.id}
    OR recipent_id = {user.id}

You Can use these 

$query = (new \yii\db\Query)->from("message_thread")
$query->orFilterWhere(['sender_id'=>$user_id])->orFilterWhere(['recipent_id '=>$user_id]);

Upvotes: 0

Related Questions