3 Replies Latest reply: Apr 27, 2012 2:43 PM by roginsb RSS

    Report on content that has received the most "likes"

    roginsb

      I am trying to report on top content (discussions, blog posts, questions & documents) that have received the most 'likes' over time. Initially I thought I could just run the query against an aggregate fact table and filter on the Like/Claim activity but the object IDs returned are not reflective of the content that was liked and instead were associated with an acclaim object. Where is the association made between the like activity and the content/object that activity took place on? Sample query for this?

        • Re: Report on content that has received the most "likes"
          Andrew Kratz

          Brian,

           

          Did you do some searches on this topic in the community?  I recall a couple of threads regarding reporting on liked content over the past couple of months.  It might point you to some folks that did this work so you can go directly to them.  Just a thought.  I never reported on liked content myself, so I would have to really dig in to figure it out.

           

          Andrew

          • Re: Report on content that has received the most "likes"
            mark.cablayan

            Hi Brain,

             

            I think this should work.  On my DB I have a lookup table that has the content types in it so I just join to that table to get the names of the content, I put a case statement on the one below to get the same results.

             

            select ja.object_id,

                case

                when object_type = 1 then 'DISCUSSION THREAD'

                when object_type = 2 then 'DISCUSSION MESSAGE'

                when object_type = 3 then 'USER'   

                when object_type = 4 then'GROUP'   

                when object_type = 13 then 'ATTACHMENT'   

                when object_type = 14 then 'COMMUNITY (Space)'   

                when object_type = 18 then 'POLL'   

                when object_type = 20 then 'PRIVATE_MESSAGE'   

                when object_type = 22 then 'ANNOUNCEMENT'   

                when object_type = 26 then 'AVATAR'   

                when object_type = 27 then 'QUESTION'   

                when object_type = 37 then 'BLOG'   

                when object_type = 38 then 'BLOGPOST'   

                when object_type = 40 then 'TRACKBACK'   

                when object_type = 41 then 'TAG'   

                when object_type = 42 then 'TAG SET'   

                when object_type = 48 then 'USER STATUS'   

                when object_type = 49 then 'USER RELATIONSHIP'   

                when object_type = 53 then 'USER RELATIONSHIP LIST'   

                when object_type = 102 then 'DOCUMENT'

                when object_type = 105 then 'COMMENT'   

                when object_type = 107 then 'RATING'   

                when object_type = 109 then 'SEARCH QUERY'   

                when object_type = 120 then 'DOCUMENT VERSION'   

                when object_type = 121 then 'DOCUMENT VERSION COMMENT'   

                when object_type = 501 then 'PROFILE IMAGE'   

                when object_type = 600 then 'PROJECT'   

                when object_type = 700 then 'SOCIAL GROUP'   

                when object_type = 800 then 'BOOKMARK'   

                when object_type = 801 then 'BOOKMARK (EXTERNAL)'   

                when object_type = 1100 then 'VIDEO'   

                when object_type = 2020 then 'USER CONTAINER'   

                when object_type = 1464927464 then 'WALL ENTRY'   

                when object_type = 3227383 then 'IDEA'   

                when object_type = 96891546 then 'EVENT'   

                else 'Other' end as object_type_name,sum(score) as total_likes

            from public.jivedw_acclaim ja

            where ja.acclaim_type = 'like'

            group by ja.object_id,case

                when object_type = 1 then 'DISCUSSION THREAD'

                when object_type = 2 then 'DISCUSSION MESSAGE'

                when object_type = 3 then 'USER'   

                when object_type = 4 then'GROUP'   

                when object_type = 13 then 'ATTACHMENT'   

                when object_type = 14 then 'COMMUNITY (Space)'   

                when object_type = 18 then 'POLL'   

                when object_type = 20 then 'PRIVATE_MESSAGE'   

                when object_type = 22 then 'ANNOUNCEMENT'   

                when object_type = 26 then 'AVATAR'   

                when object_type = 27 then 'QUESTION'   

                when object_type = 37 then 'BLOG'   

                when object_type = 38 then 'BLOGPOST'   

                when object_type = 40 then 'TRACKBACK'   

                when object_type = 41 then 'TAG'   

                when object_type = 42 then 'TAG SET'   

                when object_type = 48 then 'USER STATUS'   

                when object_type = 49 then 'USER RELATIONSHIP'   

                when object_type = 53 then 'USER RELATIONSHIP LIST'   

                when object_type = 102 then 'DOCUMENT'

                when object_type = 105 then 'COMMENT'   

                when object_type = 107 then 'RATING'   

                when object_type = 109 then 'SEARCH QUERY'   

                when object_type = 120 then 'DOCUMENT VERSION'   

                when object_type = 121 then 'DOCUMENT VERSION COMMENT'   

                when object_type = 501 then 'PROFILE IMAGE'   

                when object_type = 600 then 'PROJECT'   

                when object_type = 700 then 'SOCIAL GROUP'   

                when object_type = 800 then 'BOOKMARK'   

                when object_type = 801 then 'BOOKMARK (EXTERNAL)'   

                when object_type = 1100 then 'VIDEO'   

                when object_type = 2020 then 'USER CONTAINER'   

                when object_type = 1464927464 then 'WALL ENTRY'   

                when object_type = 3227383 then 'IDEA'   

                when object_type = 96891546 then 'EVENT'   

                else 'Other' end

            order by  total_likes desc;

            • Re: Report on content that has received the most "likes"
              roginsb

              Here's a consolidated version of the query above with object type meta lookup and includes the title of the content.

              Query most 'liked' content