MANY_MANY help search model

Hello,

I have been trying to understand how Yii deals with MANY_TO_MANY relations in vain.

To cut it short:

I have 2 tables with a many to many relationship that i’ve transformed into 3:

Table article (articleID, x, y ,z, etc) with articleID primary_key + foreign key in table tagConnector. I’ve also introduced a public proprety called $tag;

Table tagConnector (tagConnectorID, tagID, articleID) with tagConnectorID primaryKey.

Table tag (tagID, tag) with tagID foreginKey in table tagConnector.

my relations are as follows:

Class Article:

'tagConnectors' => array(self::HAS_MANY, 'TagConnector', 'articleID' , 'with'=>'tag' , 'together'=>false),

Class TagConnector:

'tag' => array(self::BELONGS_TO, 'Tag', 'tagID'),

'article' => array(self::BELONGS_TO, 'Article', 'articleID'),

Class Tag:

'tagConnectors' => array(self::HAS_MANY, 'TagConnector', 'tagID'),

my search criteria in function search():

Class Article:

$criteria->with=array('tagConnectors'=>array('select'=>'tagConnectors.tagID'));

$criteria->compare('tagConnectors',$this->tag,true);

Class TagConnector:

$criteria->with('tag');

$criteria->compare('tag.tag',$this->tagID,true);

$criteria->compare('article.articleID',$this->articleID,true);

$criteria->compare('tagConnectorID',$this->tagConnectorID,true);

Class Tag:

$criteria->compare('tag',$this->tag,true);

$criteria->compare('tagID',$this->tagID,true);

in my CGridView i managed to fetch the tags from the tag table using:

$this->widget('zii.widgets.grid.CGridView', array(

        'id'=>'article-grid',

        'dataProvider'=>$model->search(),

...

                array('name'=>'tagConnectors', 'header'=>'bla bla' ,

                     	'value'=>function ($data) { 

                                        $tagIDs="";

                                        $tmp=array();

                                                foreach ($data->tagConnectors as $tag)

                                                        $tmp[]=$tag->tag->tag;

                                                        

                                        return $tagIDs = implode(', ',$tmp);

                                        },

                                        //'filter'=>CHtml::listData(Tag::model()->findAll(), 'tag', 'tag')),

                                        'filter'=>CHtml::textField('tag')),

                array(

                        'class'=>'CButtonColumn',

                        'template'=>'{view}',

                ),

        ),

));

NOW my question is, how can i search tags from the Article Form or CGridView or any other search form.

I need to understand this more than just getting a solution, since i’ve read so many tutorials, wikis and examples and I still can’t understand the logic behind this.

THANK YOU IN ADVANCE FOR YOUR HELP

Hi,

I don’t have your answer yet, but a question/comment : are there particular reasons you didn’t use a MANY_MANY relationship :

Class Article:

'tags' => array(self::MANY_MANY, 'Tag', 'TagConnector(articleID, tagID)');

Class Tag:

'articles' => array(self::MANY_MANY, 'Article', 'TagConnector(tagID,articleID)');

Then:

$article = Article::model()->with('tags')->findByPk($articleId);

foreach($article->tags as $tag)

	echo $tag->tag;

And a guess from what I see: your tagConnector table only exists between tag and article. So you maybe should better get rid of the tagConnectorID and use the pair (tagID, articleID) as primary key.

Jean-Marie.

Merci bcp Jean-Marie.

I deleted the extra not needed pk and implemented the relations as per your advice.

I still however facing the same problem:

how can i search for articles using tags from the CGridView ?

Thanks

Hi Camino,

As far as I understand, searching by HAS_MANY or MANY_MANY relation in CGridView or in CListView ca be very challenging when your needs are a little bit complicated. We can do searching with those relations using ‘with’ and ‘together’. But in some cases we can not expect proper results if we also want to paginate the results in a grid or in a list.

This post can be very long. I would like to begin with the very basic. Please be patient.

MANY_MANY Relation

'tags' => self::MANY_MANY('Tag', 'tag_connector(Article_id, Tag_id)'),

I assume the relation above in the following. But it is the same with HAS_MANY relation.

Lazy Loading

$articles = Article::model()->findAll($condition_a);

foreach($articels as $article)

{

	foreach($article->tags as $tag)

	{

		echo $tag->tag;

	}

	$tag_text = implode(',', $tags);

	echo '<p>' . $article->title . ' : tags = ' . $tag_text . '</p>';

}

In the above, 1 + N queries will be executed.

The first one is for the articles. And the rest are for tags that are attached to the articles. Those additional queries are executed by accessing "$article->tags".

This is called "lazy loading" approach.

Note that $condition_a can only refer to the columns in Article table.

Eager Loading

You may also do it like this with "eager loading" approach:

$articles = Article::model()->with('tags')->findAll($condition_<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='B)' />;

foreach($articels as $article)

{

	... // same as above

}

This will execute just one query that retrieve the articles with their tags all at once.

And $condition_b can have some condition regarding the columns in Tag table, because the query joins Tag table with Article table.

Search by MANY_MANY Relation with Eager Loading

So you can do something like this:

$search_tag = 'php';

$articles = Article::model()->with('tags')->

	findAll('tags.tag = :tag', array(':tag' => $search_tag));

This will retrieve all the articles that have a tag named ‘php’.

Well, sounds promising, doesn’t it?

There’s one thing you have to note. All the article objects retrived in this way don’t have any tags other than ‘php’. For example, even if an article named ‘yii framework’ has 2 tags of ‘php’ and ‘framework’, the array of $model->tags contains only one tag of ‘php’, while it will contain ‘php’ and ‘framework’ in lazy loading.

Article::search() method for CActiveDataProvider

Now, let’s modify Article::search() method for CGridView:

public function search()

{

	$criteria = new CDbCriteria;

	$criteria->with = 'tags';

	$criteria->together = true; // ** IMPORTANT **

	

	$criteria->compare('t.id', $this->id); // Article ID

	$criteria->compare('t.title', $this->title, true); // Article Title

	...

	$criteria->compare('tags.tag', $this->search_tag);	// Tag

	...

	return new CActiveDataProvider(get_class($this), array(

		'criteria' => $criteria,

		'pagination' => array(

			'pageSize' => 10,

		),

	));

}

"$criteria->together = true" is important in this context.

Usually “with” is enough to apply the eager loading, but it will be ignored when the pagination is used (i.e. “OFFSET” and “LIMIT” is used). So we set “together” to true to enforce the eager loading, otherwise comparing of ‘tags.tag’ to $this->search_tag will cause an error.

You have to add ‘search_tag’ virtual property to Article model and use it in your view.

Please refer to the following wiki article for detail. It’s a must read.

Searching and sorting by related model in CGridView

Problem

Now, here comes a very difficult problem.

What if your ‘$search_tag’ is empty?

Then “$criteria->compare(‘tags.tag’, $this->search_tag)” will be ignored and all the articles will be listed in the grid.

But you will notice that the pagination is out of order. For example, page #1 may contain only 5 rows instead of 10.

Or if you compare ‘tags.tag’ and ‘search_tag’ with the 3rd parameter set to true, meaning using ‘LIKE’ clause, then the same problem will occur.

Every condition that will result in 2 or more applicable tags will cause the same problem.

This is a side effect of eager loading. The number of Article objects can be lesser than that of retrieved db records in eager loading, while they are assured to be the same in lazy loading. We can not work around it, because "LIMIT" clause is for db records, not for AR objects.

In short, we have to do eager loading for search and need lazy loading for proper pagination.

So, what should we do?

It all depends on the needs and the limitations of the app. There’s no single clear cut solution for it, IMO.

P.S. to all the fellows

Please let me know if I’m wrong in this post.

Hello softark,

Please accept my appologize for this late reply, was forced to stay in bed. Flu

I will read and try your recommendations above and will get back to you when done.

Thank you so mush for your time.

Hi Camino,

Have you read this wiki? http://www.yiiframework.com/wiki/323/dynamic-parent-and-child-cgridciew-on-single-view-using-ajax-to-update-child-gridview-via-controller-with-many_many-relation-after-row-in-parent-gridview-was-clicked/

Yes Yes, I know, yet another wiki

Hello softark,

I went throught what you’ve wrote, and re-read the “Searching and sorting by related model in CGridView” Wiki.

It looks like I have already done what you’ve recommended, however I wasn’t successfull getting any results. For a reason or another, my queries are never correct.

I have decided to go back to step 0 and re-write the entire search method …

Thank you Sir, I’ll keep you posted.

Gerhard Liebenberg Thank you for the link i shall go throught it as well