OQL- creating OQL using remote entity imported using Odata services

I am writing query  which involves local entity in module where i creating OQL using remote entity which is imported using Odata services   SELECT AP.AppNumber, AP.AappsubNumber FROM "RemoteClient.AppMaster" as AP where AP.AppNumber not in (select AppNumber FROM "LocalModule.AppMasterFinal" )   Now i am getting error    An error occurred while executing OQL: Attempted to retrieve remote sources List(com.mendix.datastorage.model.Model$ModelQueryBasedRemoteEntitySourceImpl@61ee23cb, com.mendix.datastorage.model.Model$ModelLocalEntitySourceImpl@65d1bb2e). Mixed source retrieval is currently not supported   Please advise if we can achieve  it in better way using OQL( we koved to OQL due to performance issues when we try to use microflow for tracking list operations)
1 answers

OQL is a way to retrieve persisted data from the database via the mendix object model. Remote entities are as the name suggests not persisted in your local database, so there you have your mismatch.

It seems you want to get the appnumbers from the remote entity for apps that are not in your “local” app.

I don't know how big the dataset of appnumbers is on both ends. But I see at least several options off course depending on the volumes. 

(1) get a list of all apps from the remote app and persist them in a local entity. Then you can do your magic, with a normal xpath or with the OQL.
(2) get the list of all apps from the remote app, process them (not persisted) in a loop, removing each app from the list that you also have in your local app. I would Iterate through the list that has the smalles number of items and then remove them from the other list.
(3) a variant of nbr 1 but you update the apps from the remote entity upfront or periodically via e.g. a scheduled event
(4) expose a REST API, where a POST or a GET + Queryparameter accepts a list of Appnumbers and returns all apps that are not in that list.


Unless you have a very large amount of objects in these entities, I dont see why your original microflow based solution would require a lot of time…You can maybe speed up things by: 

  • retrieving the whole list of apps from your AppMasterFinal and finding your apps from the remote entity in them by filter (instead of a retrieve from database evrytime)
  • if you still need to get the appmasterfinal objects one by one from the database, consider adding an index on the AppNumber of AppMasterFinal.

Hope this helps you.