mysql - How to add to a user-user correlations table based on whether users have rated any of the same items? -

i'm building site determines correlation values between users based on users liking or disliking posts on site. so, i'm setting database lot of materialized views (tables updated sql triggers upon alteration of other tables).

one of tables update table storing information relationship between users, based on table storing rates (likes , dislikes on posts) users have provided.

in rates table have columns user_id, post_id, , liked (1 if user liked post, -1 if user disliked post.)

in table of user-user correlations, plan on having columns user_id_a, user_id_b, agreed, disagreed, , total. agreed number of posts rated same, disagreed number of posts rated opposite, , total total number of posts have both rated.

i want set trigger on rates table when row added it, will:

• add necessary user-user correlation rows didn't exist user-user correlations table
• update agreed, disageed, , total columns in relevant rows of user-user correlation tables

edit: question how write queries listed in bullet points. know how write triggers, can't figure out how make queries go inside of them in case.

i need write similar triggers when rows in rates updated or deleted.

i'm working in mysql database.