3 Replies Latest reply on Feb 2, 2015 9:35 PM by

    Connect content to space locations

    liuxyaaron

      Hi,

       

      We are tyring to identify what space each content resides.

       

      Right now we have poll_id, blogpost_id, document_id, object_id in those content tables, we also have container_id, container_type, and even group_id, group_type data, which is about the space architecture.

       

      Is there any way we can connect this two kinds of data together so we can find out where those contents reside.

       

      Thanks!

       

      Rose D

        • Re: Connect content to space locations
          Caton Guilbault

          Hi Xinyuan,

           

          Yes you can tie an object (content) to where it lives (container). We usually do this in separate queries but you could combine into one with some intricate where statements.

           

          Here are some examples for the production database: SchemaSpy - Table db_700clean.public.jivecommunity

               Note: You can do this from the Analytics DB as well: SchemaSpy - Table dba8c1cmrana.public.jivedw_container

           

          Documents:

          select    doc.internaldocid,
                    doc.documentid, 
                    person.username as "Original Author",
                    community.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 jivecommunity community on community.communityid = doc.containerid -- This join brings in the where docs live
          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 = 'community' and  -- This where limits the results to space containers
                    jcount.objecttype = '102' -- This where limits the results to docs
          order by jcount.viewcount desc
          

           

          Blogs:

          select   jivecommunity.lft, 
                   jiveblog.name as "Blog Name", 
                   jiveblogpost.userid, 
                   jiveuser.username, 
                   jivecommunity.name as "Space Name", 
                   jiveblogpost.blogpostid, 
                   jiveblogpost.permalink,
                   to_timestamp(jiveblogpost.creationdate/1000) as "creationdate",
                   to_timestamp(jiveblogpost.publishdate/1000) as "publishdate", 
                   jiveviewcount.viewcount
          from jiveblogpost
          join jiveblog on jiveblog.blogid = jiveblogpost.blogid
          join jiveuser on jiveuser.userid = jiveblogpost.userid
          join jivecommunity on jivecommunity.communityid = jiveblog.containerid -- This join brings in the where blogs live
          join jiveviewcount on jiveviewcount.objectid = jiveblogpost.blogpostid
          where   jiveblog.containertype = 14 and -- This where limits the results to space containers
              jiveviewcount.objecttype = 38  -- This where limits the results to blogposts
          

           

          Polls:

          select  poll.pollid, 
              sgroup.name as "Group", 
              juser.username, 
              jgroup.name, 
              to_timestamp(poll.creationdate/1000) as "creationdate", 
              poll.name
          from jivepoll poll
          left join jiveuser juser on juser.userid = poll.userid
          left join jivesgroup sgroup on sgroup.groupid = poll.objectid
          left join jivegroupuser guser on guser.userid = poll.userid
          left join jivegroup jgroup on jgroup.groupid = guser.groupid  -- This join brings in the where polls live
          where   poll.objecttype = 700 and -- This where limits the results to group containers
              sgroup.name like '%MBB%'
          order by "creationdate" asc