zend-blog-3-backend

1
CREATE PROCEDURE update_comments_count(IN topicID INT UNSIGNED)
2
  BEGIN
3
    DECLARE count_comments INT DEFAULT 0;
4

5
    SELECT COUNT(DISTINCT c1.id) INTO count_comments
6
    FROM
7
      comments AS c1,
8
      comments AS c2
9
    WHERE
10
      c1.post_id = topicID
11
      AND c2.post_id = topicID
12
      AND c1.tree_left_key <= c2.tree_left_key
13
      AND c1.tree_right_key >= c2.tree_right_key
14
      AND c2.deleted = 0;
15

16
    UPDATE `posts` SET `comments_count` = count_comments
17
    WHERE `posts`.`id` = topicID;
18

19
    SELECT count_comments;
20
  END
21