Well, I have 3 tables: Products, Stores and Alerts. Table alerts have the product_id and table products have the store_id. I have made a model relation so I can get an array with the data of the 3.
The problem is, I need to add a condition where it would only return alerts where client_id (on table Stores) is equal to the id of the client trying to access the data.
$clientidx = $clientlist->idx;
$criteria = new CDbCriteria();
$criteria->with = 'product';
$criteria->with = 'product.store';
$criteria->addCondition('product.store.client_idx = :client_idx');
$criteria->params[':client_idx'] = $clientidx;
$openalerts = Openalert::model()->findAll($criteria);
Why can't I make the condition see product.store.client_idx?
In condition you should not use relation alias (product.store), but table alias. If you want to add condition for client_idx column in store table, you need:
$criteria->addCondition('store.client_idx = :client_idx');
Also this:
$criteria->with = 'product'; $criteria->with = 'product.store';
$criteria->with = 'product' has no effect, since you're overwriting this property immediately. If you want to set multiple relations, you need to pass array to $with. ['together' => true] will also ensure that correct JOIN is created for query, so your condition will work correctly:
$criteria->with = [
'product',
'product.store' => ['together' => true],
];