Hi, i have a problem with yii CActiveFinder + custom query criteria with JOINS in it and it seems to me that i hit a wall, with no solution unless i hack the yii core files (which i really don’t want to do).
If i try to run a query, where the CActiveRecord uses the CActiveFinder internally, CActiveFinder uses aliases for column names (e.g.:
SELECT `productPacks`.`id` AS `t1_c0`, `productPacks`.`rev` AS `t1_c1`, `productPacks`.`Product_id` AS `t1_c2`
And then my custom query criteria breaks, because the JOINS there reference the fields in the parent table, e.g.: t.id
And i have no idea how to get the new column alias that CActiveFinder has applied to that column
Does anyone have any idea if this is possible to fix/work around ? I have searched the Yii forums but found nothing helpful.
I will explain the issue in more detail.
So basically, i have a dabatase with Products and ProductPacks (i.e. SKU). It is actually a lot more complicated, but for simplicity’s sake lets leave it at that. Now, there is also a custom user permissions system, which filters viewable/editable items according to the user. And i have thought that it makes sense to implement that with functions, that return CDbCriteria, which contains additional filtering query criteria, based on the user that is passed (which is usually the currently logged in user).
So then, you can apply this criteria, whether you are fetching a list of items or one item. And you can apply this same criteria, when fetching items from a relation (or at least that was the idea).
In other words: keep permissions logic simple, clean & reusable (in one place). All nice & dandy.
And it all worked, until now. I have tried (for the first time) to apply that criteria, when fetching related items, e.g. like this (i just mashed up the code that illustrates the problem from various places into here):
$product = Product::model()->findByPk($someProductId); // Load the product, the ProductPacks of which we will try to fetch // Load the operator, that we will match the permissions against $operator = Operator::model()->findByPk($someOperatorId); // This is the permissions criteria $productPackViewCriteria = new CDbCriteria(); $productPackViewCriteria->mergeWith(array( 'join' => 'LEFT JOIN warehouse ON warehouse.id=t.Warehouse_id ' . 'LEFT JOIN operator_provider ON operator_provider.Provider_id=warehouse.Provider_id ', 'condition' => '(operator_provider.Operator_id=:Operator_id)', 'params' => [ ':Operator_id' => $operator->id, ], 'group' => 't.id', )); // Load ProductPacks $product->getRelated('productPacks', FALSE, $productPackViewCriteria);
// And what i get is this:
The problem is the reference to ‘t.Warehouse_id’ field, which is a field in product_pack table (used by model ProductPack). And it is now assigned the alias
t1_c3 by CActiveFinder:
`productPacks`.`Warehouse_id` AS `t1_c3`
The core of the problem is that i needed to make a custom user permissions system, and i wanted to make it reusable & centralized in one place. And after working with yii for a few months it seemed that the best way to do that, was with functions that returned CDbCriteria objects based on a given user (which would usually be the logged in user). Then you can apply that criteria, whether you are fetching a list of items, or one item, or related items.
However i only tried fetching related items today, and it didn’t work (because of CActiveFinder column aliasing).
I could rewrite the permissions system to return the criteria in some other form. The real question is:
is it even possible to pass custom CDbCriteria with JOINs that reference main table fields to
CActiveRecord->getRelated(.., .., $criteria)