6 Replies Latest reply on Dec 27, 2013 3:23 PM by spagnj

    How to connect documents table with activity table?

    erkjones

      I don't see any common keys between the documents table and any other jive table. How can I join the doc table to the activity table? I want to get a count of downloads and views of documents by community, by user.

       

      thanks for the help

        • Re: How to connect documents table with activity table?
          pbradle9

          Did you ever figure this out? I'm trying to sort out the Download reporting from my .dmp and it makes no sense. Users have Downloads for items they never Viewed and Download seems to fire 20-30 times in a row.

          • Re: How to connect documents table with activity table?
            mnevill

            This may not be exactly what you are looking for since it only accounts for views and not downloads, but here is a query that shows who looked at what: content viewed in the last month by logged in users

             

            You can probably massage that to include downloaded items (Activity Type 320) to get the report you need.

            • Re: How to connect documents table with activity table?
              spagnj

              HOPE you have a good SQL engine

               

              You asked:

              I don't see any common keys between the documents table and any other jive table. How can I join the doc table to the activity table?

              The following joins will do the job, just not most efficiently

              SELECT

              jdoclkup.*

              ,jaf.*

              ,jdc.name as community_name

              ,jdc.container_type

              ,jdc.container_id

              FROM

              ( SELECT DISTINCT ON (jd.internaldocid, jdv.versionid) jd.internaldocid, jd.documentid, jd.versionid AS current_version_id, jdv.minorversion, jdv.title AS doc_title, jdv.state AS doc_state, jdv.status AS doc_status, jdv.language AS doc_language, jdv.userid AS modifier_userid, timezone('UCT', to_timestamp(jd.creationdate / 1000)) AS doc_created_ts, timezone('UCT', to_timestamp(jdv.modificationdate / 1000)) AS doc_last_modified_ts, timezone('UCT', to_timestamp(jd.expirationdate / 1000)) AS doc_expires_ts, jd.userid AS doc_create_userid, jd.containertype, jd.containerid, jd.recommended, jd.meanrating, jd.readcount,

                      CASE

                          WHEN jd.commentstatus = 1 THEN 'disabled'::text

                          WHEN jd.commentstatus = 2 THEN 'open'::text

                          WHEN jd.commentstatus = 3 THEN 'closed'::text

                          ELSE NULL::text

                      END AS comment_status

                 FROM jivedocversion jdv

                 LEFT JOIN jivedocument jd ON jd.internaldocid = jdv.internaldocid AND jd.versionid = jdv.versionid) AS jdoclkup

                   LEFT JOIN jivedw_object jdo ON jdo.object_id = jdoclkup.internaldocid

                   LEFT JOIN jivedw_activity_fact jaf ON jaf.direct_dw_object_id = jdo.dw_object_id

                   LEFT JOIN jivedw_containter jdc ON jdc.dw_container_id = jaf.dw_container_id

              WHERE jdo.object_type IN (102)

              AND jaf.activity_type IN (10,320) --10 = views, 320 = downloads

              AND jdc.container_type IN (14)

              --AND jaf.user_id NOT IN (-1) --OPTIONAL: THIS WILL REMOVE ANONYMOUS USERS FROM ACTIVITY

               


              In order to be able to see count of document downloads/views 'by community' and/or 'by userid'  You would have to create the above as a table, and then create some aggregrate queries to count the instances within the dimensions you're looking for...