3 Replies Latest reply on Sep 30, 2014 8:50 AM by Caton Guilbault

    Has anyone been able to query ideas/discussions?

    aliceyang

      Ultimately, what I want do is be able to export a list of comments or users who have commented in a thread or an idea. Any good SQL queries that would help with this? Thanks in advance!!

        • Re: Has anyone been able to query ideas/discussions?
          Caton Guilbault

          Hi Alice,

           

          Here are some queries to get you started:

           

          Ideas posted

           

          select 
              idea.ideaid, 
              idea.userid,
              person.username,
              idea.containertype,
              idea.containerid,
              idea.status,
              to_timestamp(idea.creationdate/1000) as "creationdate", 
              to_timestamp(idea.modificationdate/1000) as "modificationdate",
              idea.ideasubject
          
          from jiveidea idea
          left join jiveuser person on person.userid = idea.userid
          

           

          Threads Posted

           

          select jivemessage.threadid, jivemessage.userid, jivecommunity.name as "Space", to_timestamp(jivemessage.creationdate/1000) as "creationdate",jivethreadlatest.messagecount, jiveviewcount.viewcount, jivemessage.subject
          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
          

           

           

          All Messages Posted

           

          select 
              message.messageid, 
              juser.username, 
              answer.helpfulanswer, 
              answer.correctanswer,
              to_timestamp(message.creationdate/1000) as "creationdate",
              community.name as "Space"
              
          from jivemessage message
          
          left join jiveanswer answer on answer.messageid = message.messageid
          left join jivecommunity community on community.communityid = message.containerid
          left join jiveuser juser on juser.userid = message.userid
          
          where message.containertype = 14 -- Limited to only Spaces 
          
          ord
          er by message.messageid asc
          
            • Re: Has anyone been able to query ideas/discussions?
              aliceyang

              Thanks this was a good start. Do you know what the query is for a commenter, as opposed to the original poster? That's really key for the desired output. I'm trying to identify the commentators in the threads.

                • Re: Re: Has anyone been able to query ideas/discussions?
                  Caton Guilbault

                  Hey Alice,

                   

                  The All Messages Posted query contains that information (all posts on all threads). You can add the threadid column from the jivemessage table to see this relationship in the results. If you are trying to figure out who the 'answerer' of questions are then I would recommended this query:

                   

                   

                  Question Answerer:

                   

                  select    question.threadid,
                            answer.messageid,
                            question.userid as "Asker",
                            answer.userid as "Answerer",
                            to_timestamp(question.creationdate/1000) as "creationdate",
                            to_timestamp(question.resolutiondate/1000) as "resolutiondate",
                            question.resolutionstate,
                            answer.helpfulanswer,
                            answer.correctanswer
                  
                  from jivequestion question
                  left join jiveanswer answer on answer.threadid = question.threadid
                  
                  order by question.threadid asc