Yii1 search（）由多对多关系返回一个相关模型

Question

I have 3 tables: clients, traders and client_trader_relation

clients can have many traders, and traders can have many clients so this is a MANY-MANY relationship with a "pivot" table. The relation is defined in clients model like this:

$relations = array(
        'traders' => array(self::MANY_MANY, 'traders', 'client_trader_relation(client_id, trader_id)'),
);

Now everything works correctly when displaying a listing of all clients in let's say CGridView, but I also want to be able to search for clients by a specific trader (so if one of the traders is let's say id 10, then return this client).

I have done it like this in model's search() function:

public function search()
{
    $criteria=new CDbCriteria;

    $criteria->with = 'traders';
    $criteria->together = true;
    $criteria->compare('traders.id', $this->search_trader);
}

search_trader is an additional variable added to the model & rules so it cna be used for searching. While this works, it successfully returns all clients of specified trader, the result doesn't contain any other related traders, just the one I'm searching for. I can understand this behaviour, because that's the way the generated SQL works.

I'm curious though if there is any way to return all the traders from such search without having to make any additional queries/functions? If not, then what would be the correct way of doing such thing? As for now, I can only think of some function in the model like getAllTraders() that would manually query all the traders related to current client. That would work, I could use this function for displaying the list of traders, but it would produce additional query and additional code.

Answer 1

You can use this to disable eager loading:

$this->with(['traders' => ['select' => false]]);

But this will create separate query for each row, so with 20 clients in GridView you will get extra 20 queries. AFAIK there is no clean and easy way to do this efficiently. The easiest workaround is to define additional relation which will be used to get unfiltered traders using eager loading:

public function relations() {
    return [
        'traders' => [self::MANY_MANY, 'traders', 'client_trader_relation(client_id, trader_id)'],
        'traders2' => [self::MANY_MANY, 'traders', 'client_trader_relation(client_id, trader_id)'],
    ];
}

And then define with settings for eager loading:

$this->with([
    'traders' => ['select' => false],
    'traders2',
]);

Then you can use $client->traders2 to get full list of traders.

---

You can also define this relation ad-hoc instead of in relations():

$this->getMetaData()->addRelation(
    'traders2',
    [self::MANY_MANY, 'traders', 'client_trader_relation(client_id, trader_id)']
);