1 Reply Latest reply on Jul 22, 2011 3:44 AM by RyanSe

    ViewCount Table - Seeing the number of times a thread has been viewed

      I was wondering if anyone had a SQL statement to find the number of times a thread has been viewed using the JiveViewCount Table in the Application DB.

       

      I am able to get a value by putting the Thread ID as the Object ID in the table but it is not the number I want.

       

               -The number I get shows all views within that thread which includes messages.

               - I am interested in the number of times just the thread has been viewed, not including the messages.

       

      Using google analytics, I am able to see the # of views on a specific Thread ONLY (disregarding message views within the thread)

      I was wondering if I could do the same with the Jive DB.

       

      Thanks

       

      Cameron

        • Re: ViewCount Table - Seeing the number of times a thread has been viewed
          RyanSe

          Hey Cameron,

           

          From what I can tell, the JiveViewCount table stores the total views that shows on the thread (for example, this thread is showing 38 views right now). I would imagine this includes views for anytime the thread gets hit. Here is a SQL query I use to pull back a "thread inventory" for our moderator team so they can see at a glance what threads exist in their areas.

           

          select jivemessage.threadid, jivemessage.userid, jivecommunity.name as "Space",jivemessage.subject, to_timestamp(jivemessage.creationdate/1000) as "creationdate",jivethreadlatest.messagecount, jiveviewcount.viewcount
          from jivemessage
          left join jivethreadlatest on jivethreadlatest.threadid = jivemessage.threadid
          left join jiveviewcount on jiveviewcount.objectid = jivemessage.threadid
          left join jivecommunity on jivecommunity.communityid = jivemessage.containerid
          where jivemessage.parentmessageid is null and jiveviewcount.objecttype = '1' and jivemessage.containertype = '14'
          order by jiveviewcount.viewcount desc

          I haven't seen anything in the tables so far that would lead me to believe that you can isolate just the root message (which is the message identified as the thread)

           

          Google Analytics or the like would be the best bet.