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.


Comments

Post a Comment

Popular posts from this blog

html5 - What is breaking my page when printing? -

-
Image
i have questioner , don't know why when printing it's creating break before first question. live demo (link) i able resolve issue removing fieldset container had wrapped in.. having fieldset element causing of fields grouped together, , since not fit on first page, being pushed second page. removing fieldset element, able firefox print first 2 questions on page 1 identically chrome. it seems not new problem firefox , fieldset not playing nice found old bugzilla ticket 2008 has persisted , still being commented on in 2014 (link) if retaining fieldset important, found solution else came here (link) <script type='text/javascript'> $(window).bind('beforeprint', function(){ $('fieldset').each( function(item) { $(this).replacewith($('<div class="fieldset">' + this.innerhtml + '</div>')); } ) }); $(window).bind(
Read more

c# - must be a non-abstract type with a public parameterless constructor in redis -

-
when save object, got below error must non-abstract type public parameterless constructor in order use parameter 't' in generic type or method 'servicestack.redis.redisclient.store<t>(t) redisclass.getinstnace().store(msg); // error here redisclass.getinstnace().save(); as third party's class, can not edit how save object? could create wrapper around third party object call constructor, store wrapper? e.g. public class mywrapper { public thirdpartyobject thirdpartyinstance { get; set; } public mywrapper() { thirdpartyinstance = new thirdpartyobject("constructors"); } }
Read more

ajax - PHP/JSON Login script (Twitter style) not setting sessions -

-
i'm having trouble getting twitter style login work properly, reason gets stuck at session_register("useremail"); session_register("userpass"); and doesn't redirect after it. if enter wrong account data, works , appropriate error message pops up. this full code: <?php if(empty($_post['email']) || empty($_post['password'])) { die(msg(0,"all fields required")); } if(!(preg_match("/^[\.a-z0-9_\-\+]+[@][a-z0-9_\-]+([.][a-z0-9_\-]+)+[a-z]{1,4}$/", $_post['email']))) die(msg(0,"you haven't provided valid e-mail")); $host="localhost"; $username="root"; $password="x"; $db_name="x"; $tbl_name="x"; mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select db"); $useremail=$_post['email']; $userpass=
Read more