Looking for alternatives: Retrieve objects from database, without an associated object

Hello, I think everybody knows of use cases in their projects where you are interested in objects for which the association to some entity is empty. Perhaps you want to look at a list of inspections that need to be executed that do not have an assigned mechanic, or a list of order lines that are not yet linked to an invoice. Without adding additional information, the only way to retrieve such a list from the database, is using a not()-constraint. And this is not strange, because you are asking the database to tell you which objects do NOT appear in a certain association table. The database can only give you the answer by first looking at which objects are in the association table and then providing you with the inverse of that result. However: Not()-constraints with an association inside are so inefficient that Mendix has provided the option within Studio Pro to look for them, because they should be limited as much as possible: However, in my opinion, it is still often used. I'm posting this ‘question’ to see what other Mendix developers use as an alternative. For things like inspections or order lines, there are of course functional solutions in giving them a certain status when a mechanic is assigned, or an invoice is linked. Another option could even be to add a dedicated boolean attribute to keep track of whether the object has an associated object and change the value in a BCo-microflow for example. One other out-of-the-box thing I could think of, which would require some help from Mendix, would be the option to be able to configure in your domain model for each association whether you would want to store a line in the association table for each object, regardless of whether the association is filled. You could then have lines in the association table for which only the left or right column is filled with the Mendix ID of the object, but this would enable the database to look through the table for all the lines that have an empty left or right column, without having to return an inverse result. Please let me know what you use in this case, or whether you always just perform the not() constraint with the assocation inside). Thanks in advance for the information. Greetings Martin
1 answers

A Simple solution in such cases is to use a workflow and retrieve the full list first and then the list where the mechanics are assigned and then you subtract the list2 from list1