Using a SQL IN operator in Mendix

Dear all,  I am coding an autocorrect-functionality using the approach presented by Peter Norvig.  In Python it runs only 10 words per second, hence it seems quite heavy.  I would like to outsource the computations of known(), edits1(), edits2() functions to the database by using SQL's IN operator.  Hence, I would like to use SQL-like queries freely in order to talk to the database, primarily the SQL's IN operator. However, the only context where I can see SQL IN operator in use is OQL in Data Sets, which then are cul-de-sac for me, as I see no way to use them (Data Sets) back in a microflow. Is there any way to use complex database retrieves in Mendix? OQL suffices, but diverting its output to the report pages only is a horrible choice, I think. Data Sets referenced in microflows? Thanks in advance.  
2 answers

Check out

it can create advanced OQL queries for you and use the data in your model to create graphs en reports or data processing


I did this many times. Easiest is the following:

  1. Create a string variable in which the (dynamic) query string with the 'IN' statement is written.
  2. Create a Java action with the queryString as an inputparameter.
  3. As an output parameter choose whatever you like (but most likely a list of objects that agree with the IN statement?)
  4. In the Java action, call the Mendix Core API function:  
  5. // Retrieve the data from the database
    IDataTable table = Core.retrieveOQLDataTable(getContext(), OqlQuery);
     After this, retrieve all the Mendix identifiers from the IDataTable and put them in a list. Then, feed that list to the Mendix Core API function:
    List<IMendixObject> objects = RetrieveObjectsByIDs(ListIDs);		
    return objects;

    to retrieve the correct objects agreeing with the IN statement.

To sum it up, it involves some Java, but is not too hard to build.

Good luck!