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.