Succesfull Inner Join On Tables

Hi there.

I’m sorry if I’m posting in the wrong topic, but this was the one that seems the best to choose.

I’m creating a portfolio which contains projects and filters.

With this filters you can ‘filter’ the projects to get for example all projects which are created with Yii and Ajax. (This will be all, but that doesn’t matter)

The database structure is:

Project : id and other fields

project_filter : project_id , filter_id

Filter : id, and other fields

what I want is query:

retrieve all projects which contain all the requested filterIds

So if someone ‘requested’ for Yii and Webdesign, then a project without Webdesign should NOT show up.

There is a relation between Project and Filter ($filterTypes).

I know how I can do this in SQL Queries, but not how I can do this with a CdbCriteria.

My code at the moment:


$criteria = new CDbCriteria();

if(count($filterIds) > 0 ){

//      $criteria->together = true;

//      $criteria->with = 'filterTypes';

	$criteria->join = 'INNER JOIN `project_filter` `pf` ON `t`.id=`pf`.project_id';

	foreach($filterIds as $filterId){

		$criteria->compare('filter_id', $filterId, false, 'AND');

	}

	$criteria->group = 't.id';

				

//	'filterTypes' => array(

//	        'condition' => 'filter_id IN ('.implode($filterIds).')',

//		'group'     => 'project_id',

//		'having'    => 'COUNT(*) = ' . count( $filterIds ),

//	));

}

	$criteria->addSearchCondition('active', 1, false);

			

	$criteria->order = 'datum_aangepast DESC';


	$data = Project::model()->findAll($criteria);

($filterIds is an array of integers)

To show you what I’ve tried allready, I’ve let the commented lines in.

I hope someone can teach me a nice tric how I can do this so I can learn more about CdbCriteria.

I hope to hear from you soon!

Thanks in advance!

P.S.

The SQL Query which works is:


SELECT `project_id` FROM `project_filter` 

WHERE `project_id` IN 

  (SELECT `project_id` FROM `project_filter` 

     WHERE `filter_id`=3 

     AND `project_id` IN 

       (SELECT `project_id` FROM `project_filter` 

        WHERE `filter_id`=<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' />

   ) 

GROUP BY `project_id`

You can use [url=&quot;http://www.yiiframework.com/doc/api/1.1/CDbCriteria/#addInCondition-detail&quot;]addInCondition/url to achive your goal.

See this thread for references: Using addInCondition with subquery

Kokomo, thanks for your reply! Thanks to the topic you send I’ve managed a workaround:


if(count($filterIds) > 0 ){

	$projectFilters = ProjectFilter::model()->findAll(

		array('select'=>'project_id',

		'condition'=>"`filter_id`=". implode($filterIds,  ' OR `filter_id`='),

		'group'=>'project_id',

		'having'=>'COUNT(`project_id`)='. count($filterIds),

	));

	$projectIds = array();

	foreach($projectFilters as $pf){

		$projectIds[] = $pf->project_id;

	}

				

	if(count($projectIds) == 0){

	// if no project correspond the criteria

	        echo $this->renderPartialCorrect('//portfolio/indexNoScript', array(

					'portfolio'		=> array()), true, true);

		exit;

	}else

	$criteria->compare('id', $projectIds);

}