cdbcriteria with complex query (union of subqueries)

Yii 1.1 General Discussion
1

Hi everyone,

there is a method I use to get some informations




$db = Yii::app()->db;

$sql = '

	SELECT qt.relation_id

	FROM

	(

	SELECT DISTINCT sa.relation_id AS relation_id

	FROM tbl_relations_alternatives_link AS sa

	JOIN tbl_ancestors AS a ON a.id=sa.ancestor_id

	WHERE a.relation_id='.$this->id.'

	UNION 

	SELECT DISTINCT a.relation_id AS relation_id

	FROM tbl_relations_alternatives_link AS sa

	JOIN tbl_ancestors AS a ON a.id=sa.ancestor_id

	WHERE sa.relation_id='.$this->id.'

	UNION

	SELECT DISTINCT sal.relation_id AS relation_id

	FROM tbl_relations_alternatives_link AS sa

	JOIN tbl_ancestors AS a ON a.id=sa.ancestor_id

	JOIN tbl_relations_alternatives_link AS sal ON a.id=sal.ancestor_id

	WHERE sa.relation_id='.$this->id.' AND sal.relation_id!='.$this->id.'

	) AS qt';

$results = $db->createCommand($sql)->queryAll();


if (!empty($results)) {

	$relations_ids = array();

	foreach($results as $row) {

		$relations_ids[] = $row['relation_id'];

	}

	$relations_list = implode(",", $relations_ids);

	$criteria = new CDbCriteria;

	$criteria->condition = 'id IN ('.$relations_list.')';

	$criteria->order = 'relation ASC';

	$alternative_relations = Relations::model()->findAll($criteria);

	$string = '';

	foreach ($alternative_relations as $alternative_relation) {

		$string .= '<strong>'.CHtml::link($alternative_relation->relation, $alternative_relation->generateUrl()).'</strong>, ';

	}

	return substr($string, 0, -2);

}		

return '';

As you can see I have 2 queries: the first one is a complex union of three subqueries, the second one is very simple.

What I would is improve performance, with only one complex query.

Is it possible to get object results (using cdbcriteria) with a very complex query?

In fact the "rule" of my second query is only to get the object results, otherwise the first one with createCommand method gives me array.

Very thanks