0

I have 3 tables: clients, clients_traders_relation, traders

This is a many-many relation, clients can have many traders and traders can have many clients.

I have 2 relations in the clients model:

'traderssearch' => array(self::MANY_MANY, 'traders', 'clients_traders_relation(relation_id, trader_id)'),
'traders' => array(self::MANY_MANY, 'traders', 'clients_traders_relation(relation_id, trader_id)'),

The traderssearch relation is used for limiting access to clients that don't belong to the current trader (so traders see only their clients, or if they are one of the traders). The traders relation is used for obtaining all the traders (this is a solution I got from my other question, because one relation would only return the current trader, even if there are more.

It's used in defaultScope like this:

return array(
    'alias' => $class,
    'with' => array(
        'traderssearch' => array(
        'together' => true,
        'condition' => 'traderssearch.id='. Yii::app()->user->getId()),
    'traders',
),

However, I'd like to have the ability to search for clients of some other trader. So let's say I have 3 clients:

  1. Client A, traders: me, XYZ
  2. Client B, traders: me
  3. Client C, traders: XYZ

I want to search for client's of XYZ. It should return only Client A, because that's the only client related both to me and XYZ.

I did it like this in search():

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

search_trader is an additional variable added to the model so it can be used for searching. If I display a list of clients without using search, it works okay. But if I try to search for client's of XYZ, it returns me nothing. i think that is because both of these conditions are excluding themselves, something like id = 10 AND id = 20 which will never be true. I tried adding an OR to compare() but it didn't work.

If I remove the condition from defaultScope, searching works because now there's only one condition searching for trader id. Is there any way to keep both the defaultScope and search conditions? Would I need another, third relation?

AJ Cole
  • 169
  • 2
  • 13
  • Yes, there is a way. Yesterday I had the same issue hahaha But first, what is the difference between the 2 relations in the `clients` model? It looks duplicate. Second, did you try using the same `SELECT` query and running it on the database? Creating the correct query first and then translate to Yii code helps a lot to verify where the problem is – GusMilc May 08 '19 at 12:37
  • @Milk the second relation is duplicate, because it's used to retrieve all related traders for displaying. The second relation is used just for filtering the results in search() and defaultScope() because otherwise I wouldn't be able to retrieve all traders. I don't know what the current query is, I'm not executing any custom queries just trying to use default behaviour of search() and defaultScope() and they work seperately, but applied together they somehow conflict with each other – AJ Cole May 08 '19 at 13:02
  • You can see the query with something like: https://stackoverflow.com/questions/33344202/print-out-a-sql-single-query-yii-1-x – GusMilc May 08 '19 at 13:12
  • Try something like: `$criteria->addCondition("t.relation_id = \"traderssearch\".relation_id AND \"traderssearch\".trader_id = " . $xyz);` where `$xyz` is the other trader id and `t` is the `tableName` of your model. – GusMilc May 08 '19 at 13:24
  • I dont know your columns, so I can only hope the above comment can guide you – GusMilc May 08 '19 at 13:26
  • 1
    But that condition is basically what these relations do already. I tried printing out the query in search() but the commandBuilder produced a very simple query and not what is really executed. As for now I got around this problem by creating a third duplicate of the same relation, so now: `traderssearch` is for defaultScope(), `traderssearch2` is for search() and `traders` is for retrieving all traders. It works correctly, although I don't really enjoy it but I think it may be the only way around this. – AJ Cole May 08 '19 at 13:52

0 Answers0