Xpath where all associated objects meet all conditions

Like writers, I’m having a big developers block in something that feels incredibly easy. I’m struggling with writing an xpath that targets objects where all associated objects meet the conditions, instead of at least one of the associated objects. Or phrased differently, for example purposes, I want a list of Customers that haven’t placed an order in the last 6 months. I.e., retrieve all customers which have no associations with an invoice object or where all associated invoice objects have an InvoiceDate of older or equal to CurrentDateTime-6months. Basic Xpath retrieves with conditions over association simply return objects where at least one associated object returned true. So if a customer has two invoices, one of 9 months old and one placed last week, he will still be returned. When an object has an one-to-many relation, the xpath checks each whether at least one of them matches the conditions. If so, it returns true. In my case, however, all association objects should return true. An inelegant solution would be to simply retrieve all customers, loop over them and for each, do a retrieve by first, sorted by InvoiceDate descending checking if the invoiceDate is older than 6 months or not. If it has a hit, the customer is an old one. However, the database in question holds tens of thousands of customers that require multiple of these conditions. Doing those retrieves sequentially is simply unacceptable in regard to performance. So how do I fulfil this seemingly simple task of creating an xpath that only returns objects where all associated objects on a relation match true?
2 answers

Did you think about using the oposite statement?

Instead of

[MyModule.EntityA/EntityA_EntityB/EntityB.Attribute = $Value]

you could say:

[not(MyModule.EntityA/EntityA_EntityB/EntityB.Attribute != $Value)]
[MyModule.EntityA/EntityA_EntityB/EntityB.Attribute = $Value]

That means that at least one object needs to have this value, but no object is allowed to have a different one.

Note that depending on the complexity of your associations and the size of your table, using not() over an association may slow down the request.


Would it not be simple to substract from the total customer list the list with customers that have placed an order in the last 6 months? Because those are two simpel retrieves.