Yii Framework Forum: cdbcriteria with complex query (union of subqueries) - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

cdbcriteria with complex query (union of subqueries) Rate Topic: -----

#1 User is offline   pippo30 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 50
  • Joined: 25-October 13

Posted 29 April 2017 - 01:21 PM

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
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users