[Coldbox 5.1.1] - ORM and executeQuery returning nested array

Communities ColdBox HMVC
1

Hello,

I am using ColdFusion 2016 and mysql.

I have the following code which works, but returns data as a nested array making it awkward to work with. Is there a way to return this data in maybe an array of structs or something different ? HQL doesn’t seem to let me return my data as a query even though that’s what I’d like, so I am getting this nested array.

prc.populateBlogPost=ormService.executeQuery( “select b.articleTitle,b.datePublished,b.articlePhotoPath FROM BlogPost b WHERE b.datePublished >= ? and b.datePublished <= ? ORDER BY Rand()”, [’#prc.stMonthData.monthbegin#’,’#prc.stMonthData.monthend#’],0,3);

Perhaps another method instead of executeQuery?

Any thoughts or ideas would be great.

Thanks in advance!

2

You have a number of options. If you just want to return a query, it’s easy enough to run it through a standard CFML query.

My preference, for what you want to do, would be to use a criteria with a result transformer, which will return an array of structs with only the keys requested:

var q = blogPostService.newCriteria()

.isGE( prc.stMonthData.monthbegin )

.isLE( prc.stMonthData.monthend)

.withProjections( property=“articleTitle,datePublished,articlePhotoPath” );

return q.resultTransformer( q.ALIAS_TO_ENTITY_MAP ).list( sortOrder=“Rand()”);

If you run list() with an asQuery=true argument, however, you will receive a query back. It won’t be restricted to only the three columns, though.

HTH,

Jon

3

Thanks for pointing me down this path. Now I can feel more confident getting the data I need out. I can always resort back to regular SQL, but I am striving to stick with the ORM for this project and this criteria builder stuff looks pretty cool.

4

For some reason I am getting an error with that code:

The PROPERTYVALUE parameter to the isGE function is required but was not passed in.

I am definitely passing in the value to the isGE method, I even tried just hardcoding it for kicks. I will do some digging and see if I can troubleshoot, but thought I’d ask.

Thanks again for your help!.

5

Well, I figured it out, I needed to specify the property name then the value so this works:

.isGE(‘datePublished’,prc.stMonthData.monthbegin) versus .isGE(prc.stMonthData.monthbegin)

6

Oops! Sorry about that. I dashed that code sample off and forgot to put the property names in. Glad you got it working!

Jon

– -- You received this message because you are subscribed to the Google Groups “ColdBox Platform” group. For News, visit For Documentation, visit For Bug Reports, visit — You received this message because you are subscribed to the Google Groups “ColdBox Platform” group. To unsubscribe from this group and stop receiving emails from it, send an email to . To post to this group, send email to . To view this discussion on the web visit . For more options, visit .