How do I query a list of records that have no child records?

In SQL I would write something like this:

SELECT * FROM parent WHERE parent_id NOT IN (SELECT parent_id_fk FROM child)


SELECT * FROM parent WHERE NOT EXISTS (SELECT 1 FROM child WHERE parent_id_fk = parent_id)

In Appian expression language I can find children with no parents by finding records with null in the relationship as described here:

The example in the documentation is like so:

  recordType: recordType!Employee,
  filters: a!queryFilter(
    field: recordType!Employee.relationships.teams,
    operator: "not null"
  pagingInfo: a!pagingInfo(
    startIndex: 1, batchSize: 100

However this is not what I want, I want to find parents with no children.

I think it might look something like this:

  recordType: recordType!Parent,
  filters: a!queryFilter(
    field: recordType!,
    operator: "not in",
value: forEach(
items: a!queryRecordType(
       recordType: 'recordType!Children',
       fields: 'recordType!Children.fields.parentId',
       pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 5000)
expression: fv!item["parentId"]
), pagingInfo: a!pagingInfo( startIndex: 1, batchSize: 100 ) ).data

This query doesn't even seem to work, it says

"Expression evaluation error at function a!queryRecordType: Cannot apply operator [NOT_IN] to field [parentId] when comparing to value [TypedValue[it=3125,v={}]]"

However, even if it did work, it would break when there were more than 5000  child records because this is the maximum batch size and it wouldn't filter parent records that matched the excluded results, so I would have to make it a sort of correlated subquery and filter the children to return only rows with a matching parent for each parent record.

I feel like this is much more complicated than it should be.

So, please, how do I get a set of all records with no child records?

  Discussion posts and replies are publicly visible