1 Reply Latest reply on Jun 16, 2014 11:47 AM by Caton Guilbault

    Container table for comments

    don840

      I want to be able to trace the comments to the community space they were posted in. The jivecomments table tells me the comments for the object (blog, doc, etc.) How do I join this table to get the community space these comments were posted in. I am using the application DB.

        • Re: Container table for comments
          Caton Guilbault

          Hey Donna,

           

          Check out the jivecommunity table. The query below pulls in all comments on documents specifically but you could expand to other content types.

           

          select     com.commentid, 
                    doc.documentid,
                    community.name,
                    com.userid,
                    person.username,
                    to_timestamp(com.creationdate/1000) as "creationdate"
          from jivecomment com
          left join jiveobjecttype objtype on objtype.typeid = com.objecttype
          left join jiveuser person on person.userid = com.userid
          left join jivedocument doc on doc.internaldocid = com.objectid
          left join jivecommunity community on community.communityid = doc.containerid
          where com.objecttype = '102'
          order by commentid asc