Hi there. I just started using Yii and ActiveRecord (was a long time cakephp user) and could use some insight. I’m creating a web app similar to shutterstock, not for revenue but keeping track of my organizations stock art and digital assets. A taxonomy system is essential, but I’m a little concerned with how many queries its taking me to update an existing File record. My relevant relations are as follows:
File -> Many_Many -> Category (using a CategoryFile intermediary table)
File -> Many_Many -> Tag (using a TagFile intermediary table)
Tags are taken in from a comma separated text field and split into an array (the user can add or remove tags for a file). Heres a the chunk of code in my actionEdit method:
TagFile::model()->deleteAll('file_id=:fid', array('fid'=>$model->id)); $tags = $_POST['File']['tags']; if(!empty($tags)): $tagArray = Tag::string2Array($tags); foreach($tagArray as $tag): $tagModel = Tag::model()->find('name=:name', array(':name'=>$tag)); if(!$tagModel): $tagModel = new Tag; $tagModel->name = $tag; $tagModel->save(); endif; $tagFile = new TagFile; $tagFile->file_id = $model->id; $tagFile->tag_id = $tagModel->id; $tagFile->save(); endforeach; endif;
As you can see, at best there are (n x 2) + 1 queries and at worst (n x 3) + 1 queries. For 50 tags, thats up to 151 queries just for saving related tag data. Is there a better way to do this? I want a rich tag database like how shutterstock uses keywords so I can bring relevant results to the user.