Hello all -
In the Yii framework, I’m trying to figure out how to query and get back results that match multiple tags in the following system:
posts
id
tags
id
post_tags
post_id
tag_id
I’m trying to figure out how to return a result of posts which match tag 1 AND tag 2.
I’ve been searching forever, I can get posts that match 1 tag, but can’t figure out how to match N tags. Any help with this would be much appreciated! I will post the solution if I find it.
Now if anyone can figure out how to get this working with CActiveDataProvider I would appreciate it!
So far I seem to have it working like this:
In Post relations:
array(
'tags' => array( self::MANY_MANY, 'Tag', Yii::app()->db->tablePrefix . 'post_tag(post_id,tag_id)' ),
);
In PostTag relations
array(
'post' => array( self::BELONGS_TO, 'Post', 'post_id' ),
'tag' => array( self::BELONGS_TO, 'Tag', 'tag_id' ),
);
In my Controller
$tags = array( 1, 2, 3 ); // Match N tags
$criteria = new CDbCriteria();
$criteria->with = 'post';
$criteria->addInCondition( 'tag_id', $tags );
$criteria->group = 'post_id';
$criteria->having = 'COUNT(*) = ' . count( $tags );
$matchedPostTags = PostTag::model()->findAll( $criteria );
foreach( $matchedPostTags as $matchedPostTag )
{
$post = $matchedPostTag->post;
// Do what you want with it.
}
Got it working, for anybody curious:
Post relations
array(
'tags' => array( self::MANY_MANY, 'Tag', Yii::app()->db->tablePrefix . 'post_tag(post_id,tag_id)' ),
'post_tags' => array( self::HAS_MANY, 'PostTag', 'post_id' ),
);
PostTag relations
array(
'post' => array( self::BELONGS_TO, 'Post', 'post_id' ),
'tag' => array( self::BELONGS_TO, 'Tag', 'tag_id' ),
);
Controller
$tags = array( 1, 2, 3 ); // Match N tags
$criteria = new CDbCriteria();
$criteria->with = array(
'post_tags' => array(
'condition' => 'tag_id IN ('.implode($tags).')',
'group' => 'post_id',
'having' => 'COUNT(*) = ' . count( $tags ),
)
);
$criteria->together = true;
$dataProvider = new CActiveDataProvider( 'Post', array(
'criteria' => $criteria,
) );
This is now working with my ListView to show only posts that match ALL specified tags.