Dear devs
take a situation where a part of the database diagram looks like the image below:
(by the way above image is a printscreen from MySQL workbench program)
The target is to get all the names from ‘owner’ who have a certain priority. AND also belong in category A and/or category B and/or category C
So we have the name from ‘owner’, priority is inside parent. And then we have an array of categories which determine inside which categories we will search
As you may have guessed, parent has only one child. There is no situation where, for example, childB and childC have the same parent.
Please don’t be too judgemental on my db design Either way it cannot change at this point in time.
Trying to build the above statement with php code and usage of CDbCriteria below is the code which I currently use.
It seems really inefficient, using lots of foreach loops, seems like I am going back to the non-sql days.
<?php
public static function getNames($priority, $categArray=null)
{
if($categArray==null) $classes = Parent::getCats();
else $classes = Parent::getCats($categArray);
$parent_ids = array();
$textField = 'parent_id';
$criteria = new CDbCriteria;
$criteria->select = array($textField);
foreach($classes as $class)
{
$models = $class::model()->findAll($criteria);
foreach($models as $model)
{
$parent_ids[] = $model->$textField;
}
}
$textField = 'name';
$criteria=new CDbCriteria;
$criteria->select = array($textField);
$criteria->with = array(
'parents' => array('condition' => "priority=$priority"),
);
$criteria->addInCondition('parents.id', $parent_ids);
$models = self::model()->findAll($criteria);
$names = array();
foreach($models as $model)
{
$names[] = $model->$textField;
}
return $names;
}
?>
note: above function is inside Owner Model class
Do you know any other more efficient ways I could build it using CdbCriteria or pure SQL ?..
The above situation is a more complex situation of one of my previous topics inside the forum:
PS: I believe a nice tutorial with a name like "from SQL to CDbCriteria" would be a must. Because there are lots of situations where CDbCriteria saves the day, but lots of developers join Yii framework already knowing more or less how to build SQL queries