2 Replies Latest reply on Jun 11, 2014 2:19 PM by don840

    Core Application DB- Count of Discussions created in the User Group

    don840 Jun 10, 2014 12:27 PM

      Hi All,

      I have written a query that tells me count of Discussions by the create date.  I am also able to breakdown the counts by jive community space, eg, 'Support'. I am unable to isolate this for Special User Groups. In jive community, special user groups show as blank. How can I get the counts of discussions posted under a special user group? . I need to know what all tables to join to get to the special user group names, and the # of threads under each.

       

      My original query is below:-

      SELECT
      jivemessage.threadid,  --jivemessage.subject,
      jivecommunity.name,

      MM') as "msgcreatedate"
      , count (CASE WHEN jivemessage.parentmessageid is null AND jivequestion.threadid is NULL THEN 1 ELSE NULL END) as "# Disucssions"
        
      FROM   
      jivethread LEFT JOIN jivemessage ON jivethread.threadid = jivemessage.threadid
      LEFT JOIN jiveuser ON jiveuser.userid = jivemessage.userid
      LEFT JOIN jivecommunity ON jivethread.containerid = jivecommunity.communityid

      Where 1=1
      group by jivecommunity.name, 
      to_char( to_timestamp(jivemessage.creationdate/1000) , 'YYYY-MM')
      order by to_char( to_timestamp(jivemessage.creationdate/1000) , 'YYYY-MM')desc

      • 268 Views
      • Tags:
        • Re: Core Application DB- Count of Discussions created in the User Group
          Caton Guilbault
          Caton Guilbault Jun 10, 2014 2:32 PM (in response to don840)

          Hi Donna,

           

          Since Jive has multiple group types we wrote this so the user could specify the one they are looking for in the where statements. The primary group tables are jivesgroup, jivesgroupmember, and jivesgroupprop.  I don't have a query handy for threads but here is the one we use for documents. Replacing the jivedocuemnt table with jive thread should be fairly straightforward.

           

          select 
    doc.internaldocid,
    doc.documentid, 
    person.username as "Original Author", 
    sgroup.name as "Space Name",
    to_timestamp(doc.creationdate/1000) as "creationdate",
    to_timestamp(docversion.creationdate/1000) as "VersionCreatedate", 
    to_timestamp(docversion.modificationdate/1000) as "Modificationdate",
    docversion.userid as "Last Modified UserID", 
    jcount.viewcount,docversion.title
    
from jivedocument doc
left join jiveuser person on person.userid = doc.userid
left join jiveobjecttype objtype on objtype.typeid = doc.containertype
left join jivesgroup sgroup on sgroup.groupid = doc.containerid
left join jivedocversion docversion on docversion.internaldocid = doc.internaldocid
left join jiveviewcount jcount on jcount.objectid = doc.internaldocid

where docversion.state = 'published' and objtype.code = 'socialgroup' and jcount.objecttype = '102'

order by jcount.viewcount desc
          1 person found this helpful
            • Re: Core Application DB- Count of Discussions created in the User Group
              don840 Jun 11, 2014 2:19 PM (in response to Caton Guilbault)

              Thanks Caton. That helped. Below is the updated query -

               

              SELECT

              to_char( to_timestamp(jivemessage.creationdate/1000) , 'YYYY-MM') as "msgcreatedate" 

              , count (CASE WHEN jivemessage.parentmessageid is null AND jivequestion.threadid is NULL THEN 1 ELSE NULL END) as "# Disucssions"                          

              FROM   

              jivethread LEFT JOIN jivemessage ON jivethread.threadid = jivemessage.threadid

              LEFT JOIN jiveanswer ON jivemessage.messageid   = jiveanswer.messageid

              LEFT JOIN jivequestion ON jivethread.threadid   = jivequestion.threadid

              LEFT JOIN jiveuser ON jiveuser.userid = jivemessage.userid

              LEFT join jiveobjecttype objtype on objtype.typeid = jivethread.containertype

              LEFT join jivesgroup on jivesgroup.groupid = jivethread.containerid

              Where

              1=1

              and jivesgroup.grouptype IN ('1', '2')

              group by

              to_char( to_timestamp(jivemessage.creationdate/1000) , 'YYYY-MM')

              order by to_char( to_timestamp(jivemessage.creationdate/1000) , 'YYYY-MM')desc