Function to find records without children records

[MGanon]

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?

[DirkRedeker]

Hi

I recommend the following.

1.read all groups with name and sysid and their parent field into an array.

2.Add a haschildren flag to the array, and set all values to false first. 

3.Then, loop the array.

4. For each group, do an inner loop, to search all groups, if any of them in the inner loop has the parent set to the sysid of the current group in the outer loop.

5.If you found a child, exit the inner loop, and set the outer group in the haschildren flag to true.

6.Loop all groups in the outer loop afterwards and copy all of the ones without children (the ones with the haschildren flag set to false) to your target array, that you want to pass to your functions

Let me know if that answered your question and mark my answer as correct and helpful, please. 

BR 

Dirk 

[MGanon]

That sounds like it would work but I also found 2 other methods: aggregate & arrayUtils.

[MGanon · Accepted solution]

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

1. DISTINT list of parents by querying groups and collecting the parents into an array using either:
2. 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/
3. 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];
      }

[DirkRedeker]

Hi

Great.

Well done, and thank you for the feedback.

Have fun!

BR Dirk