[ColdBox SEEK 3.7.0] Retrieving ORM query's criterias?

Communities ColdBox HMVC
1

Hi All,

I’m trying to return a simple struct made of:

  • records from query using ORM
  • array of criterias used in that ORM query

Query executes OK and returns the relevant records.

However retrieving the criterias is an issue: I’m expecting this:

`
[“brand=Volvo”]

`
but getCriterias() always returns an empty array.

`
[]

`

Coldbox documentation is no help on this. What I’m doing wrong?

Alternatively is there a way to retrieve the full underlying SQL string with parameters?
Purpose is to JSONify the structure to have it returned in a browser so logging in the console in not an option here.

Thanks for any help!

-Kermit

`
var oEntityService = entityNew(“Car”);
var oCriteria = oEntityService.newCriteria();
var oEntity = oEntityService.new();
var oRestrictions = oEntity.getRestrictions();

oCriteria.add( oRestrictions.eq(“brand”, “Volvo”) );

result.aResult = oCriteria.list();
result.aCriteria = oCriteria.getCriterias(); // always returns an empty array

`

2

Hi Kermit-

Re: getCriterias(), see this thread: Redirecting to Google Groups

The short answer is that this appears to be no-longer-used code, so it will always return an empty string.

To retrieve the SQL, you could do something like this (very quickly tested, so haven’t had a chance to run it through any real testing):

var c = NoteService.newCriteria();
c.isEq( ‘Note’, 'Something ');
var criteriaImpl = c.getNativeCriteria();
var entityName = c.getEntityName();
var thesession = c.getNativeCriteria().getSession();
var factory = thesession.getSessionFactory();
var persister = factory.getEntityPersister( entityName );
var influencers = createObject( “java”, “org.hibernate.engine.LoadQueryInfluencers” ).init( factory );
var t = createObject( “java”, “org.hibernate.loader.criteria.CriteriaQueryTranslator” );
// translator
var translator = t.init(
factory,
c.getNativeCriteria(),
entityName,
t.ROOT_SQL_ALIAS
);
// join walker
var walker = createObject( “java”, “org.hibernate.loader.criteria.CriteriaJoinWalker” ).init(
persister,
translator,
factory,
c.getNativeCriteria(),
entityName,
influencers
);
// get sql
var sql = walker.getSqlString();

Since all criteria will get paramed automatically, you’ll still get the “?” in the SQL string that’s returned from this.

Hope this helps

Joel

3

You can also retrieve the positional parameter values like so:

`
translator.getQueryParameters().getPositionalParameterValues();

`

And this will give the positional parameter type:

translator.getQueryParameters().getPositionalParameterTypes()

Conceivably, you could get the SQL string (from the previous code snippet), find all instances of the replacement parameter (?), and then replace it with the correct positional value. You could also use the correct positional type to properly place the parameter value (e.g., quotes for strings, 1/0 for boolean, etc). This would be necessary, of course, because all that you get back from getPositionalParameterValues() is the raw, un-SQL-ed value:

Something like this would work:

`

var types = translator.getQueryParameters().getPositionalParameterTypes();
var values = translator.getQueryParameters().getPositionalParameterValues();
var dialect = factory.getDialect();
var sqlValues = [];
for( var i=1; i<arrayLen( types ); i++ ) {
var type = types[ i ];
var value = values[ i ];
arrayAppend( sqlValues, type.objectToSQLString( value, dialect ) );
}

`

And will produce an array like this:

It would be nice, I think, to add this functionality to CB’s CriteriaBuilder. I know that I’ve wanted to be able to retrieve the SQL as well apart from just logging it.

I’ll create a ticket.

Hope this helps!

4

https://ortussolutions.atlassian.net/browse/COLDBOX-203

5

Hi Joel, thanks so much for your support!

+1. I do agree such functionnalities would be extremely useful - at least Get execution SQL string (parameters replaced).

Cheers,
-Kermit

6

Sure thing, glad it’s helpful!