Function to find records without children records

MGanon · ‎09-27-2019

How can I generate a unique list of parent group ids that I can store into an array to use as filter for another query?

I am looking for a simplified way to identify active groups with a certain type that do not have any children. I know that I can query the group table for the active groups then re-query the group table again for groups that have the first group as a parent then further process the first group if the second query returned no records. The issue is performance with several thousand active groups. Even during off-peak hours, I don't want to run a query that calls a another query several thousand times.

I originially intended something like:
var itiltype = GetIDValue('sys_user_group_type','itil');
var pgrp = new GlideRecord('sys_user_group');
var cgrp = new GlideRecord('sys_user_group');
      pgrp.addQuery('active','true');
      pgrp.addQuery('type',itiltype);
      pgrp.Query();
      while (pgrp.next()){
            cgrp.addQuery('active','true');
            cgrp.addQuery('parent',pgrp.sys_id);
            cgrp.Query();
            if (cgrp.next()){ //group has child group
                   //ignore this line
            } else { //group has no children group
                   pgrp.active = 'false'
                   pgrp.update();
            }
      }

This is the recursive query that I want to avoid. I would prefer to query the group table once to generate a unique list of parent, store that list as an array

Is there a ServiceNow function similar to a SQL subquery? Something like:
SELECT id FROM tableX
WHERE active='true'
AND id NOT IN
(SELECT parent FROM tableX WHERE active='true')

I later tried to a single query the children groups with cgrp.groupBy('parent') and push the results into an array. That didn't work. Hopefully the next step would be 1 single query for the parents not in the parent array.

What is the most efficient method to identify groups with no children?

MGanon · ‎09-27-2019

I was able to reduce the recursive queries and loops down to 2 queries:

DISTINT list of parents by querying groups and collecting the parents into an array using either:
1. arrayUtils
2. 1. This appears to be the older but still very useful, especially for other functions. For a DISTINCT list, it remove duplicates after the while loop finishes.
  2. https://community.servicenow.com/community?id=community_blog&sys_id=412d26e5dbd0dbc01dcaf3231f96198e
  3. https://docs.servicenow.com/bundle/newyork-application-development/page/app-store/dev_portal/API_reference/ArrayUtil/concept/c_ArrayUtilAPI.html
3. Aggregate count
4. 1. Probably more efficient than arrayUtils for generating a DISTINCT list as it groups the list before the while loop even starts.
  2. I didn't expect "COUNT" to work since I didn't need to know the count but it appears to have worked very well
  3. https://www.servicenowguru.com/scripting/gliderecord-distinct-query/
Identifying groups without children by excluding the parent array (In our case, we use different types for potential parent & potential child groups.

Here is the script that I created showing both options:

var itiltype = GetIDValue('sys_user_group_type','itil');
//var arrayUtil = new ArrayUtil(); // not necessary for Aggregate functions
var prntid = "";
var prntids = [];
//var cgrp = new GlideRecord('sys_user_group'); //works with arrayUtil.unique('field')

//collect a distinct list of all parents from all (cgrp) groups with a parent
var cgrp = new GlideAggregate('sys_user_group');
     cgrp.addQuery('active',true);
    cgrp.addNotNullQuery('parent'); //not necessary for arrayUtils option
    cgrp.addAggregate('count'); //not necessary for arrayUtils option
     cgrp.orderByAggregate('count'); //not necessary for arrayUtils option
     //cgrp.orderBy('parent'); //not necessary for Aggregate option, may not be necessary for arrayUtils option either
     cgrp.groupBy('parent');
     cgrp.query();
     while(cgrp.next()){
        prntid = cgrp.parent;
        prntids.push(prntid+"");
     }
//prntids = arrayUtil.unique(prntids); //unnecessary with Aggregate

//query the active itil-type groups that not listed above as parents
var pgrp = new GlideRecord('sys_user_group');
     pgrp.addQuery('active','true');
     pgrp.addQuery('type',itiltype);
     pgrp.addQuery('sys_id','NOT IN',prntids); //exclude cgrp groups that are parents of groups
     pgrp.orderBy('name');
     pgrp.query();
     while (pgrp.next()){
           //[perform some processing here, such as setting active=false];
     }

View solution in original post

MGanon · ‎09-29-2019

Additional documentation for GlideAggregate: https://docs.servicenow.com/bundle/jakarta-application-development/page/script/glide-server-apis/concept/c_GlideAggregate.html?title=GlideAggregate