Aggregate fetch entities that are missing som related record
Application Type
Reactive
Service Studio Version
11.10.19 (Build 41208)

How do we, using only aggregates, fetch records that lack a certain "type" of related record in another entity, in a many-to-many relationship? Let me explain it a bit more. The Scenario below, I have Companies and Persons. These are related to eachother throug a CompanyContacts table, and these records also have a CompanyContactTypes static entity that further specifies the relationship of that record. 

As you see in the top-middle, Companies 3 and 4 have no Manager

How can I retrieve Company 3 and 4, based on the fact that even though they have associated persons, they have no manager? Intuitively I would probably solve this with a "not exist" or "not in" sub-query in a database, but is there an aggregate way of retrieving these rows?

I have currently made an action that loops through all the Companies, and in a loop I check with another action if the company "Has-a-Manager" (returns boolean), and then fill a list (structure) based on that. But this is terribly slow.

Any tips for me on this?

Hi Andras,

is it possible to share the OML?

Maybe joining with a WITH and excluding the records with Identifiers <> null will work but I need to test it.

Regards

mvp_badge
MVP
Solution

Hi Andras,

You'd typically solve this by using a With or Without join (left join) and then checking, in the Filters for NullIdentifier().

Hi Andras,

is it possible to share the OML?

Maybe joining with a WITH and excluding the records with Identifiers <> null will work but I need to test it.

Regards

mvp_badge
MVP
Solution

Hi Andras,

You'd typically solve this by using a With or Without join (left join) and then checking, in the Filters for NullIdentifier().

Thank you both for the pointers - I will try this out. I was actually trying out the Advanced SQL route for this now, but your suggestion looks like a good way to solve it.

@José I haven't tried sharing .oml files before, so I was thinking of creating a new smaller scenario to describe the problem, and then to share that one. However, your tips point me towards the more clean solution, and it seems that you both agree that this is the route to take. 

I will post again if I'm unsuccessfull with this approach. Thanks!

mvp_badge
MVP

One further pointer: if you need a nested inner join on an outer join, you should place the inner join ("Only With") above the outer join ("With or Without") in the Aggregate joins.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.