I want to increment a post->commentCounter after creating a new comment for a post.
This action will be executed in comment->afterSave(), right?
load the post + save $post->commentCounter++
query the comment table SELECT COUNT() FROM comment WHERE postId = :postId, then load post + update commentCounter
using a transaction
The result of your action should be a single UPDATE statement like this: UPDATE post SET comment_counter = comment_counter + 1 WHERE id = x;
This way no transaction is required.
So you could do:
$post = post::model()->findByPk($postId);
$post->comment_counter = new CDbExpression('comment_counter + 1');
Or you could do it even shorter using Yii’s built in updateCounters function
post::model()->updateCounters(array('comment_counter' => 1), 'id = :id', array(':id' => $postId));
FYI, this can be done via
UPDATE `postTable` SET `commentCount`=(SELECT COUNT() FROM `comment` WHERE `postId`=:postid) WHERE `postId`=:postid
This way you can save up to 2 queries, since ar record doesn’t have to be loaded and resaved.
Thus I also recommend sluderitz’s suggestion, to use updateCounters().
ok, thank you both, problem solved