Yii Framework Forum: Succesfull Inner Join On Tables - Yii Framework Forum

Jump to content

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

Succesfull Inner Join On Tables Get the projects with all the filterIds Rate Topic: -----

#1 User is offline   Reinier 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 10
  • Joined: 24-April 12

Posted 05 December 2012 - 01:40 PM

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`=8)
   ) 
GROUP BY `project_id`

0

#2 User is offline   kokomo 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 281
  • Joined: 23-July 10

Posted 20 December 2012 - 10:42 AM

You can use addInCondition() to achive your goal.

See this thread for references: Using addInCondition with subquery
0

#3 User is offline   Reinier 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 10
  • Joined: 24-April 12

Posted 23 December 2012 - 07:46 AM

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);
}

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