4D Database ORDA Query: Query Using Length of Related Records

Question

Background

I'm using 4D Database (v17.5) and trying to query records. I have the following simplified schema:

Customers (table)
    -CustomerID (field)
    -CompanyName (field)
    -City (field)
    -State (field)

SalesOrders (table)
    -CustomerID (field)
    -OrderTotal (field)

They are tied together with a Many to One automatic relation:

[Customers]CustomerID (O)<-------------------------(M) [SalesOrders]CustomerID
            (ParentCustomer)              (ChildSalesOrders)

I can query to get a set of [SalesOrders] records using either of the following:

ds.SalesOrders.query("OrderTotal > 500 AND ParentCustomer.State = 'New York'")
ds.Customers.query("State = 'New York' AND ChildSalesOrders.OrderTotal > 500").ChildSalesOrders

Problem

Some customers have no orders at all, and if I want to find a set of customers with zero related orders, I am having trouble.

I have gotten the following to work:

$voCustomersToCheck:=ds.Customers.query("State = 'New York')
$voCustomersWithoutOrders:=ds.Customers.newSelection()

For each($voCustomer;$voCustomersToCheck)
    If ( $voCustomer.ChildSalesOrders.length > 0 )
        $voCustomersWithoutOrders.add($voCustomer)
    End if
End for each

What I'd like to do instead is something like:

$voCustomersWithoutOrders:=ds.Customers.query("State = 'New York' AND ChildSalesOrders.length = 0')

That doesn't work, though.

Question

Is what I'm trying to accomplish possible?

Answer 1

In v17.5, it is impossible.

That said, if you update to v18 or higher, you can use the NULL keyword; so the solution is to write:

$voCustomersWithoutOrders:=ds.Customers.query("State = 'New York' AND ChildSalesOrders = NULL')

Answer 2

Classic 4D can do this. I'm not sure about ORDA. Here is an example ;

CREATE EMPTY SET([Companies];"NoSales")

ALL RECORDS([Companies])
CREATE SET([Companies];"AllRecsSet")

ALL RECORDS([Invoices])
RELATE ONE SELECTION([Invoices];[Companies])

CREATE SET([Companies];"InvoiceSet")

DIFFERENCE("AllRecsSet";"InvoiceSet";"NoSales")

USE SET("NoSales")

CLEAR SET("InvoiceSet")
CLEAR SET("InvoiceSet")
CLEAR SET("NoSales")