Is there a way to get the number of comments for each user and update it in the number_of_comments column automatically?

I have two tables in MySQL like this

Users -> user_id , user_name , number_of_comments
Comments -> comment_id , comment , user_id

Is there a way to get the number of comments for each user and update it in the number_of_comments column automatically?

Solution 1:

Not recommended, but solves nevertheless. For learning purposes only.

CREATE TRIGGER tr_ai_update_n_of_comments
AFTER INSERT ON comments
FOR EACH ROW

UPDATE users
SET number_of_comments = ( SELECT COUNT(*)
                           FROM comments
                           WHERE comments.user_id = NEW.user_id )
WHERE user_id = NEW.user_id;

If the rows in comments may be updated (with user_id value changing) and/or deleted then create similar AFTER DELETE and AFTER UPDATE triggers.

PS. I strongly recommend you to remove users.number_of_comments column at all and calculate actual comments amount value by according query when needed.

If you agree that the value may be approximate (slightly different from the exact one), then you can use an incremental trigger.

CREATE TRIGGER tr_ai_update_n_of_comments
AFTER INSERT ON comments
FOR EACH ROW

UPDATE users
SET number_of_comments = number_of_comments + 1
WHERE user_id = NEW.user_id;

But just in case, provide for the creation of a service stored procedure (or event) that will periodically recalculate the accumulated value.