Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Note that with 7,000 managed objects, which includes 4000 users and 3000 roles, accounting for 12,000 relationships - the following query returns in about 1/4 a second on a macbook pro.

 

...

  'repo/internal/role/' AS resourcecollection 
FROM
   (
      SELECT
         mng.* 
      FROM
         openidm.managedobjects mng 
         JOIN
            openidm.objecttypes o 
            ON ( mng.objecttypes_id = o.id 
            AND o.objecttype = 'managed/user' ) LIMIT
100     )
   AS mng 
   LEFT JOIN
      (
         SELECT
            vf.rel_fullobject,
            vf.firstid AS relationshipid,
            vf.secondid AS objid,
            m.objectid 
         FROM
            openidm.pivot_relationships vf 
            JOIN
               openidm.managedobjects m 
               ON ( vf.secondid = concat('managed/user/', m.objectid) 
               AND vf.secondpropertyname = 'authzRoles' ) 
            UNION ALL
            SELECT
               vs.rel_fullobject,
               vs.`secondid` AS relationshipid,
               vs.`firstid` AS objid,
               m.objectid 
            FROM
               openidm.pivot_relationships vs 
               JOIN
                  openidm.managedobjects m 
                  ON ( vs.`firstid` = concat('managed/user/', m.objectid) 
                  AND vs.`firstpropertyname` = 'authzRoles' )
      )
      rel 
      ON ( mng.objectid = rel.objectid ) 
   LEFT JOIN
      openidm.internalrole mng_role 
      ON ( mng_role.objectid = replace(rel.relationshipid, 'repo/internal/role/', '') ) 
   
UNION ALL
   SELECT
      mng.objectid,
      mng.fullobject,
      rel.rel_fullobject AS relationshipprops,
      mng_role.objectid AS relationshipid,
      mng_role.fullobject AS relationshipobj,
      'authzRoles' AS propname,
      'managed/role' AS resourcecollection 
   FROM
      (
         SELECT
            mng.* 
         FROM
            openidm.managedobjects mng 
            JOIN
               openidm.objecttypes o 
               ON ( mng.objecttypes_id = o.id 
               AND o.objecttype = 'managed/user' ) LIMIT 100
       )
      AS mng 
      LEFT JOIN
         (
            SELECT
               vf.rel_fullobject,
               vf.firstid AS relationshipid,
               vf.secondid AS objid,
               m.objectid 
            FROM
               openidm.pivot_relationships vf 
               JOIN
                  openidm.managedobjects m 
                  ON ( vf.secondid = concat('managed/user/', m.objectid) 
                  AND vf.secondpropertyname = 'authzRoles' ) 
               UNION ALL
               SELECT
                  vs.rel_fullobject,
                  vs.`secondid` AS relationshipid,
                  vs.`firstid` AS objid,
                  m.objectid 
               FROM
                  openidm.pivot_relationships vs 
                  JOIN
                     openidm.managedobjects m 
                     ON ( vs.`firstid` = concat('managed/user/', m.objectid) 
                     AND vs.`firstpropertyname` = 'authzRoles' )
         )
         rel 
         ON ( mng.objectid = rel.objectid ) 
      LEFT JOIN
         openidm.managedobjects mng_role 
      JOIN
         openidm.objecttypes ro 
         ON ( mng_role.objecttypes_id = ro.id 
         AND ro.objecttype = 'managed/role' ) 
         ON ( mng_role.objectid = replace(rel.relationshipid, 'managed/role/', '') ) 
      
UNION ALL
      SELECT
         mng.objectid,
         mng.fullobject,
         rel.rel_fullobject AS relationshipprops,
         mng_role.objectid AS relationshipid,
         mng_role.fullobject AS relationshipobj,
         'roles' AS propname,
         'managed/role' AS resourcecollection 
      FROM
         (
            SELECT
               mng.* 
            FROM
               openidm.managedobjects mng 
               JOIN
                  openidm.objecttypes o 
                  ON ( mng.objecttypes_id = o.id 
                  AND o.objecttype = 'managed/user' ) LIMIT 100
          )
         AS mng 
         LEFT JOIN
            (
               SELECT
                  vf.rel_fullobject,
                  vf.firstid AS relationshipid,
                  vf.secondid AS objid,
                  m.objectid 
               FROM
                  openidm.pivot_relationships vf 
                  JOIN
                     openidm.managedobjects m 
                     ON ( vf.secondid = concat('managed/user/', m.objectid) 
                     AND vf.secondpropertyname = 'roles' ) 
                  UNION ALL
                  SELECT
                     vs.rel_fullobject,
                     vs.`secondid` AS relationshipid,
                     vs.`firstid` AS objid,
                     m.objectid 
                  FROM
                     openidm.pivot_relationships vs 
                     JOIN
                        openidm.managedobjects m 
                        ON ( vs.`firstid` = concat('managed/user/', m.objectid) 
                        AND vs.`firstpropertyname` = 'roles' )
            )
            rel 
            ON ( mng.objectid = rel.objectid ) 
         LEFT JOIN
            openidm.managedobjects mng_role 
         JOIN
            openidm.objecttypes ro 
            ON ( mng_role.objecttypes_id = ro.id 
            AND ro.objecttype = 'managed/role' ) 
            ON ( mng_role.objectid = replace(rel.relationshipid, 'managed/role/', '') ) 
         
UNION ALL
         SELECT
            mng.objectid,
            mng.fullobject,
            rel.rel_fullobject AS relationshipprops,
            mng_role.objectid AS relationshipid,
            mng_role.fullobject AS relationshipobj,
            'reports' AS propname,
            'managed/role' AS resourcecollection 
         FROM
            (
               SELECT
                  mng.* 
               FROM
                  openidm.managedobjects mng 
                  JOIN
                     openidm.objecttypes o 
                     ON ( mng.objecttypes_id = o.id 
                     AND o.objecttype = 'managed/user' ) LIMIT 100
             )
            AS mng 
            LEFT JOIN
               (
                  SELECT
                     vf.rel_fullobject,
                     vf.firstid AS relationshipid,
                     vf.secondid AS objid,
                     m.objectid 
                  FROM
                     openidm.pivot_relationships vf 
                     JOIN
                        openidm.managedobjects m 
                        ON ( vf.secondid = concat('managed/user/', m.objectid) 
                        AND vf.secondpropertyname = 'reports' ) 
                     UNION ALL
                     SELECT
                        vs.rel_fullobject,
                        vs.`secondid` AS relationshipid,
                        vs.`firstid` AS objid,
                        m.objectid 
                     FROM
                        openidm.pivot_relationships vs 
                        JOIN
                           openidm.managedobjects m 
                           ON ( vs.`firstid` = concat('managed/user/', m.objectid) 
                           AND vs.`firstpropertyname` = 'reports' )
               )
               rel 
               ON ( mng.objectid = rel.objectid ) 
            LEFT JOIN
               openidm.managedobjects mng_role 
            JOIN
               openidm.objecttypes ro 
               ON ( mng_role.objecttypes_id = ro.id 
               AND ro.objecttype = 'managed/user' ) 
               ON ( mng_role.objectid = replace(rel.relationshipid, 'managed/user/', '') ) ;

...