Trying to build a complex SQL query using CDbCriteria

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:

http://www.yiiframework.com/forum/index.php?/topic/22765-counting-all-rows-of-modela-which-exist-in-modelb/page__p__111132__fromsearch__1#entry111132

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

Hi pligor,

That doesn’t seem too complex, If I understand correctly you want to pull grandchild relations of owner or the reverse. What are your relations (php), how did you set up your FKs (sql).

Also have you checked that sqlWB did generate the correct FKs? Because sometimes it doesn’t, even though that usually happens when you have multiple FKs from a single table referencing the same PK.

Guessing that the implementation went well your Parent model should have following relations:

[list=1]

[*]owner BELONGS_TO =>Owner.id

[*]childA HAS_ONE => ChildA.id

[*]childB HAS_ONE => ChildB.id

[*]childC HAS_ONE => ChildC.id

[/list]

so if you have loaded a Parent object into var $parent doing

$parent->childA

$parent->childB

$parent->childC

will lazy load it’s childs. Note: two of the above will be FALSE and one will have an object in it

If you want to actively load them on each Parent model load you could set Parent’s defaultScope to include them

eg:

public function defaultScope()

    {

        return array(

            'with'=>'childA',

            'with'=>'childB',

            'with'=>'childC',

        );

    }

But You should check that this is working because I don’t know if Yii will honor the default scope of an actively loaded relation (You are loading Owner right?).

Anyway, even if it doesn’t work it still will load the child relations lazily every time you access them.

access in this example would simply be

$owner->parent->childA // returns FALSE or ChildA object

$owner->parent->childB // returns FALSE or ChildB object

$owner->parent->childC // returns FALSE or ChildC object

BTW: why can’t you change the db? Oh and it’s nice to seem more greeks getting into Yii.

PS: I reviewed the PHP code you pasted but it seems to have little or no relation to the data model you have attached. Please specify what data getCats() pulls (it is used as a class name I see so I am guessing it is pulling child names) when posting problems it is best to clearly define what you are trying to do and from what a scenario you are trying to do it.

correction to self: according to documentation the default scope will be honored even in relational queries.

Hi kiriako!

I’m glad as well to see greeks inside Yii. We should probably create a facebook group or something for us greek Yii devs only, in order to communicate and cooperate better, don’t you think?

Anyways back to work now!

I hope I didn’t confused you much as I see that all your guesses were correct.

Yes indeed two of the children are false and only one returns a model. That’s why I created a custom property for Parent model that is named getChild(), so simply $parentModel->child retrieves the current child (one should be available unless Parent isNewRecord is true)

Thanks for pointing out scopes for me. Maybe the default scope is not the best way to go, but a simple named scope would help.

Just for clarification, yes $categArray is just a parameter for defining which of the categories you wish, A and/or B, and/or C

Inspired by my own post of which the link you have seen above, i tried to shorten the sql queries to only 2 (3+1=4 sql queries was too much)

Here is the new php code which has exactly the same result, only more efficiently and more Yii related:

[php]

<?php

public static function getNames($priority, $categArray=null)

{

	if($categArray==null) $classes = Parent::getCats();

	else $classes = Parent::getCats($categArray);

	

	$textField = 'id';

	$criteria = new CDbCriteria;

	$criteria->select = array($textField);

	$criteria->with = array(

		'childA',

		'childB',

		'childC'

	);

	

	$conditions = array();

	foreach($classes as $class)

	{

		$relation = lcfirst($class);

		$conditions[] = "$relation.parent_id IS NOT NULL";

	}


	$criteria->addCondition($conditions, 'OR');

	

	$criteria->addCondition("priority=$priority");

	

	$models = Parent::model()->findAll($criteria);

	

	$parent_ids = array();

	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;

}

Of course I could always achieve the same result with one simple SQL query but the point is to learn to build better CDbCriteria, or combination CDbCriteria in order to have more Yii related php code