1 Reply Latest reply on Dec 9, 2013 12:29 PM by Caton Guilbault

    using SQL on the activity tables, how can I roll up the subspaces into the top-level space?

    erkjones

      I am using this query to get counts of views for a month, but I to see the sum of counts by space (including the subspaces), not all of the subspaces displayed separately:

       

      FROM         jivedw_activity_agg_month_sf INNER JOIN

                            jivedw_container_sf ON jivedw_container_sf.dw_container_id = jivedw_activity_agg_month_sf.dw_container_id INNER JOIN

                            jivedw_month_sf ON jivedw_month_sf.month_id = jivedw_activity_agg_month_sf.month_id INNER JOIN

                            jivedw_user_sf ON jivedw_user_sf.user_id = jivedw_activity_agg_month_sf.user_id

      WHERE     (jivedw_month_sf.year = 2013) AND (jivedw_activity_agg_month_sf.activity_type = 10) AND (jivedw_container_sf.container_type = 14) AND

                            (jivedw_user_sf.email NOT LIKE '%<internal email addresses>%') AND (jivedw_month_sf.month_of_year = 11)

      GROUP BY jivedw_container_sf.name

       

      thanks for any help with this!

        • Re: using SQL on the activity tables, how can I roll up the subspaces into the top-level space?
          Caton Guilbault

          Hi erkjones,

           

          This is tricky I've been playing with this recursive lookup query for a while trying to get it to work for multiple levels of hierarchy.

           

          WITH RECURSIVE folders(communityid, lft,rgt, name, displayname, fullname) AS (

          SELECT communityid, lft, rgt, name, displayname, 'http:// your root URl here /' || displayname || '/' AS fullname FROM jivecommunity

          UNION ALL

          SELECT jc.communityid, jc.lft, jc.rgt, jc.name, jc.displayname, fullname || jc.displayname || '/'

          FROM jivecommunity jc, folders f WHERE

          jc.lft>f.lft AND jc.rgt<f.rgt

          )  SELECT * from folders ORDER BY fullname;

           

          It works for spaces that are a couple of levels deep but anything more than three seems to fail. My alternative is to pull the space tree from jivecommunity with lft & rgh then adding in the top space manually.