Can I use OQL in any place other than with the reporting widget in the modeler?

1
I am trying to see if I can use OQL in any place other than with the reporting widget in the modeler, for example is it possible to use it in an activity in a microflow?    I would like to query a database using OQL because there is a particular operator (LIKE) that I would like to use to compare strings.  Right now, I am trying to use some string functions calls such as ‘contain’ to compare strings to each other.   I think there would be an advantage to using the LIKE operator because I can use it to compare a value to similar values using a wildcard operator.   By using the contain function call, I am trying to determine whether a substring occurs in another string which is not exactly what I would like to do, I really would like to compare a string to see if it is similar to another string.  Do I have to use the reporting widget in order to query a database or is there a function call in Mendix that serves the same function as the LIKE operator in OQL?
asked
5 answers
1

I wrote this a while back to do what you are asking with a Microflow: https://modelshare.mendix.com/models/fa101212-5b7c-4546-ae28-ec0ad891b473/sub_twostringpatternrecognizer. I also wrote a blog explaining it at http://www.nolanramsey.com/blog/2016/8/11/pattern-recognition-and-optimization-in-mendix. Good luck!

answered
1

Monique,

I am not sure, but it seems to me that Like in OQL and Contains in XPath yield the same result.  From the documentation, Like does the following

 

And the docs for XPath Contains:

So if I have a string I am looking for, lets say 'Mendix', using like '%Mendix%' with OQL will return the same results as contains('Mendix') in XPath.  Do you see different results than this?  Or are you trying to search with wildcards in the middle of the search term?

Also, as Ronald mentioned, I have implemented the Lucene search engine, which is very powerful and includes more Like searching capabilities than either OQL or XPath and offers additional capabitilities like fuzzy search.  In addition, it can index entities and attributes from your domain model but can also index the contents of documents in your Mendix app.

Hope that helps,

Mike

answered
1

OQL is very fast and very powerful because it is directly translated into a SQL statement.

 

You can create a java action with input OQL and result : List of MyEntity. The OQL must return the id's of MyEntity. For example

FROM MYMODULE/MYENTITY AS ME
SELECT ME/ID
WHERE ME/Name like '%search%' OR MA/Group like '%search%'

You can use powerful options like NOT IN to compare tables without associations.

 

FROM Contracten.Contract AS CT
LEFT JOIN Integratie.ImportContract AS IC ON (CT/Nummer = IC/Nummer) 
WHERE (IC/ID = NULL) AND (CT/Actief = ''true'')
SELECT CT/ID AS _ID 
LIMIT 500

The implementation of the java action is this (for all entities)

 

		List<IMendixIdentifier> ids = new ArrayList<IMendixIdentifier>();
		IDataTable dataTable = Core.retrieveOQLDataTable(getContext(), oql);
		for (IDataRow row : dataTable.getRows()) {
			Object value = row.getValue(getContext(), 0);
			if (value instanceof IMendixIdentifier) {
				ids.add((IMendixIdentifier) value);
			}
		}
		List<IMendixObject> objectlist = Core.retrieveIdList(getContext(), ids);
		return objectlist;

 

answered
0

By default it's not possible to use OQL query's in the modeler in for instance Microflows. There is a module for that: https://appstore.home.mendix.com/link/app/1528/Appronto/Reporting-4-Mendix

answered
0

You could also look at this module: https://appstore.home.mendix.com/link/app/3099/FlowFabric-BV/Lucene-text-search

Might give you also the sollution you are looking for.

Regards,

Ronald

 

answered