2 Replies Latest reply on Mar 10, 2010 1:05 PM by mark.williams@jivesoftware.com

    Identifying Parent/Children Relationships


      Use Case:

      I want to perform reporting limited to the sub-spaces that exist for a specific space.  For example, we have broken out Information Technology spaces (we refer to as communities).  I want to report metrics on all sub-communities under this space.


      Two Questions:


      1) Data Warehouse Tables

      I don't see any tables/fields that give me the parent/child/left/right tree relationships.  If these exist, please detail which ones I can use.



      2) SBS Operational Tables 

      I thought that I was on to something with the left/right attributes in the jivecommunity table - but these values do not seem to coorespond to the id's that I expected.  The relational diagrams don't seem to address how the tables support the parent/child relationships.  Can you please detail this out?


      Thanks, Tom.

        • Re: Identifying Parent/Children Relationships

          I am still interested in help on this one.

          • Re: Identifying Parent/Children Relationships



            1. Community specific attributes such as parent/child relationships is not available in 3.0. However, these attributes have been brought into analytics in 4.0. There is a new dimension table called jivedw_community which contains the lfg and rgt columns from jivecommunity.


            2. You are correct that it is the lft and rgt columns that are used for retrieving the descendants on a community. In order to select all descendants for a particular community you would perform a query like this:



            select communityid from jivecommunity where lft < x and rgt > y


            where x = the parent community's lft value and rgt = the parent communty's rgt value.


            If you want to include the starting parent community itself then you can change the comparison operators to <= and >= respectively.