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.