How to filter sub reference sets from each other (efficiently) in memory?

Hi guys, I have a *-* (many to many) relation between City and Person ( person_city). One city has many persons and one person is associated to multiple cities. Reference set with both as owners. When I want to filter persons in the personlist A with the city from the citylist (based on the reference set person_city) I DO NOT get the filtered person with that city , while I’d expect that. BUT when I do filter the reverse, filtering the cities from the personlist based I DO get the filtered the cities of the person list. I played further with it and it seems that the “filter” or “equal” list operation expects a corresponding subset of the reference set in order to filter it accordingly. So my question is, is there an efficient way for filtering persons based on a list of city, whereby I want to retrieve the persons associated to a city list with multiple cities.   Example; Person: A → Rotterdam, Den Haag B → Rotterdam, Amsterdam Personlist: A, B Citylist: Rotterdam, Den Haag , + 200 cities   Operation: filter personlist (A,B) with citylist (just Amsterdam). → Personlist (B) I’d expect that filter personlist/person_city with citylist (Amsterdam) would result in a personlist with just B, but unfortunately this is not the case (0 results) and I do not want to iterate over all the persons/cities to build a filtered list one by one.   there should be an efficient memory operation for this filter to get work. EDIT 1: Domain model is as simple as:  (Person) *-* (City). Reference set owners
2 answers

Why the hard requirement to do the filtering in memory?

It is easy and extremely quick using xpath:

Introduce a city-group entty and create a one-to-many between the group and the cities.

Now you can do a retrieve of all persons with associated with the city-group via a city.

Would that be a solution?



I ran into the same problem and there seems to be no solution except introducing an additional entity to replace the many-to-many association like Michiel described. That actually works fine! 

Anyway in case you can’t use an additional entity you could try using the OQL module from the marketplace! It requires some familiarization but especially for easy use-cases, like the one you described, it shouldn’t be too difficult. 

There even is an OQL playground provided by Mendix ( where you can test the OQL functionality.