I’m trying to fetch data from four tables. The relations are that action_group HAS_MANY action_group_data as well as action, while action HAS_MANY action_data. Here’s the criteria and query:
<?php
$criteria = new CDbCriteria;
$criteria->condition ='controller_id=:cid ';
$criteria->condition.='and action_group.visible=1';
$criteria->params = array(':cid'=>$controller->id);
$criteria->order='action_group.sort asc';
$side_nav = action_group::model()->with(array(
'data'=>array('condition'=>'??.language=:lang', 'params'=>array(':lang'=>$language)),
'action'=>array('condition'=>'??.visible=1', 'order'=>'??.sort asc'),
'action.data'
))->together()->findAll($criteria);
The *data tables are i18n content while action_group and action is used to define a menu (and ultimately pages) in a simple CMS.
What I wanted with the above was to fetch content in a specific language (working) for the action_groups and actions which are visible. However, all actions are returned each time.
The SQL for this query has action joined twice. On the first one the visible condition is applied, but the action_data is joined on the second.
SELECT ... LEFT OUTER JOIN `action_group_data` t1 ON t1.`action_group_id`=`action_group`.`id` LEFT OUTER JOIN `action` t2 ON t2.`action_group_id`=`action_group`.`id` LEFT OUTER JOIN `action` t3 ON t3.`action_group_id`=`action_group`.`id` LEFT OUTER JOIN `action_data` t4 ON t4.`action_id`=t3.`id` WHERE ... t2.visible=1 ...
When I iterate over the data to output a menu I do something like this:
<?php foreach($side_nav as $item): ?> <li><h3><?php echo $item->data[0]->header; ?></h3></li> <?php foreach($item->action as $link): ?> <li><a href="#"><?php echo $link->data[0]->link_text; ?></a></li> <?php endforeach; ?> <?php endforeach; ?>
The menu appears as you would expect if you would not have had a condition clause on visible. The action_groups are excluded when I set visible=0 on them in the db, but all actions are always shown.
How can I get around that action is joined twice?