I'm trying to construct a JDOQL query (using datanucleus) that will search for matches of a parent class based on criteria in an owned one-to-many child class. The query looks like this:
Query lQ = lPm.newQuery("select from "+Module.class.getName()+
" where moduleMappings.contains(m)" +
" && showNameParam.matches(m.criteria.trim())");
lQ.declareVariables(ModuleMapping.class.getName()+" m");
lQ.declareParameters("String showNameParam");
lRet = (List<Module>) lQ.execute("StarTrek");
My data set looks something like this:
The query never matches on anything! However, if I replace the argument to the matches JDOQL method with a literal:
Query lQ = lPm.newQuery("select from "+Module.class.getName()+
" where moduleMappings.contains(m)" +
" && showNameParam.matches('.*StarTrek.*')");
Things will work for that single example, and my query will find Module[2]. What am I missing? Am I not allowed to use the contents of a mapped field as the argument to a JDOQL method? Do I need to escape things in some way?
Dave
So I figured this out, although to me it seems like a bug in either JDOQL or datanucleus. When using a mapped field as the argument to the matches method, the generated SQL does not translate the JDOQL syntax to the syntax of the data store (in my case, SQL). So in my example above, if I change the criteria fields to use SQL wildcard syntax rather than JDOQL syntax things will start to work.
Specifically, if in my example above I use criteria="%StarTrek%" rather than criteria=".\*StarTrek.\*" the JDOQL queries will start to match.
This doesn't seem right to me as different data stores could use different matching syntax, but this workaround gets me moving again...