Retrieving a list of many-to-many associated objects in an optimized way

0
I have two database entities like A <*----* B. Relationship between A & B is many-to-many, and objects  in B refer to objects in A.   Now, I have a list of A objects, I need to retrieve objects of B that are associated with objects of A in the list.   I have tried using the retrieval in loop, but it is not performant, as the list of A objects may contain 2000 to 3000 objects. How to write an optimized XPath/OQL query for this?
asked
4 answers
1

The best practice for this case is to use a new helper entity to have an entity A and B and a third one called helper which has associations A *-1 Helper and B *-1 Helper and constraint your xpath using the helper, however if this doesn't work you might need to loop and to find the object from list A based on it, the third and the most optimal way is to use the query that Tim sent you

answered
0

The best way to accomplish this is by changing the association so that both entities refer to each other.  Then you can retrieve via XPath using the association only.

answered
0

I have tried, as you said, but that didn't work. I have attached the screenshots below:

 

image.png

 

image.png

 

image.png

 

image.png

 

I know its dumb to put list of objects in XPath, but what should I put here instead of that list?

answered
0

The retrieve-activity does not support your requirement. Retrieving the objects B having an association to one of the objects A in the A-list. Mendix has no other activity doing this.

You can accomplish this by importing the OQL-module and execute this OQL:

from YourModule.B b
where b/YourModule.B_A/YourModule.A/IdAttribute in (from YourModule.A where <yourwherequeryforlistA> select A/IdAttribute) 
select *

 

answered