1 Reply Latest reply on Aug 6, 2014 12:30 PM by datasci

    Who likes who? i.e. How to join users' activities with other users

    datasci

      The definitions of activity codes found here and in the meta table are very helpful, but to take it a step further -- what's the best way to associate a user's LIKE with the user whose content they're liking? In other words, how do I know who likes who?

       

      I have the same question for other actions, but if I can figure it out for a LIKE then I'm sure the others will become apparent.

       

      Using R and SQL I was able to do a join to figure out who is liking what content, but I'm still lacking who likes who...

       

      acclaim_content_user <- sqldf("select a.*, b.subject, b.container_type, b.container_id, c.* from jivedw_acclaim a, jivedw_content b, jivedw_user c where a.object_id = b.content_id and b.user_id = c.user_id")

       

      Even my first step here must be imperfect though, because jivedw_acclaim has a couple of thousand likes but the result of the join above gives me only a few hundred likes...

       

      UPDATE


      I thought I had it figured out... this seems like it should work, but I end up with 0 results...


      content_author <- sqldf("select a.direct_dw_object_id, a.user_id as author from jivedw_activity_fact a  where activity_type = 20")

       

      content_liker <- sqldf("select a.direct_dw_object_id, a.user_id as liker from jivedw_activity_fact a  where activity_type = 101")

       

      who_likes_who <- sqldf("select * from content_author a, content_liker b where a.direct_dw_object_id = b.direct_dw_object_id")


      I also tried using the indirect object ID for content_liker, but still got no matches...