1 Reply Latest reply: Apr 27, 2012 1:24 PM by roginsb RSS

    Return most "liked" content with Titles

    roginsb

      I am trying to build a query that returns a list of content with the most "likes" which runs fine as the following query against the application database:

      SELECT* FROM JIVEUSER.JIVEACCLAIM acc WHERE acc.acclaimtype = 'like' ORDER BY acc.score DESC

       

      However, this returns a list of objects using the objectid and object type to uniquely identify each result but not the name or title of the content. How can the name or subject of each piece of content be retrieved? ie. The title of a blogpost, the subject of a discussion message, the title of a discussion thread etc...?

        • Re: Return most "liked" content with Titles
          roginsb

          Silly mistake, in order to achieve the desired result you have to join the object table on both the id and type, otherwise you get quite a few results the from the acclaim table of varying types all associated to the same object id. Hope this helps someone else.

          SELECT acc.object_id,
                 om.object_name AS object_type,
                 o.name,
                 SUM(score) AS total_likes
          FROM JIVEANALYTICSUSER.JIVEDW_ACCLAIM acc
          INNER JOIN JIVEANALYTICSUSER.JIVEDW_OBJECT o ON o.object_id = acc.object_id AND o.object_type=acc.object_type
          INNER JOIN JIVEANALYTICSUSER.JIVEDW_OBJECT_META om ON o.object_type = om.object_type
          WHERE acc.acclaim_type = 'like'
          AND o.object_type IN(1,27,102,38,800,801)
          GROUP BY acc.object_id, om.object_name, o.name
          ORDER BY  total_likes DESC