EntityToQuery does not return a ColdFusion Query

Last week I was working on an ORM enabled application where I was returning an array of Product objects. Working with objects is great, however I wanted a feature on my product listing page that allowed the user to filter products by price range. Now I could do a simple fixed banding, but really the price bands should be determined by the products I'm displaying. Simple I thought, instead of doing another lookup to the database, I'd use EntityToQuery and then do a Query of Queries on the result to find the most expensive product and the least expensive product.

When I tried to do somthing like this:

<!--- get product objects as an array --->
<cfset products = ORMExecuteQuery( "from Product" )>

<!--- convert to a query so we can re-query the result without calling the database again --->
<cfset qProducts = EntityToQuery( products )>

<!--- get the max and min price to build dynamic price filter --->
<cfquery name="qPriceRange" dbtype="query">
  SELECT Max( sellingcost ) as highestCost, Min( sellingcost ) as LowestCost
  FROM qProducts
</cfquery>

<!--- carry on and loop through products array and display... -->

I got the error:

java.lang.ArrayIndexOutOfBoundsException

After dumping out the query to make sure the columns existing and scratching my head a few times I posted my frustration on twitter and Ben Nadel asked me what the underlying java type was being used. Sure enough a standard ColdFusion Query returns:

coldfusion.sql.QueryTable

whereas a query returned from EntityToQuery has a java type of:

coldfusion.orm.EntityQueryTable

So, it does seem that you can't use Query of Query with EntityToQuery which is a real shame!

• Posted in:
• ColdFusion
• Hibernate

21 comments

1. Have you filed an ER for this? Actually I'd report it as a bug.
   
   Comment by Raymond Camden – June 22, 2010
2. This bug has already been reported and has been fixed in the updater that will be released soon.
   
   Comment by Rupesh Kumar – June 22, 2010
3. @Rupesh - awesome!!! Thanks for letting me know :)
   
   Comment by John Whish – June 22, 2010
4. It looks like coldfusion.orm.EntityQueryTable has a parent class of coldfusion.sql.QueryTable. I'm not good enough with Java to know but isn't there a way to cast a variable as its parent class?
   
   WriteDump(CreateObject('java','coldfusion.org.EntityQueryTable'));
   
   Comment by David Boyer – June 22, 2010
5. I wonder if you could do this as a temporary solution until the updater is released:
   
   qProducts = JavaCast('coldfusion.sql.QueryTable', EntityToQuery( products ) );
   
   I have not tried it, but I think it would work. Unless coldfusion.orm.EntityQueryTable adds something that it just cannot work without.
   
   Comment by Jason Dean – June 23, 2010
6. Rupesh is the man :) Didn't have much else to add on this one, so I figured I'd state the obvious.
   
   Also, did anyone try that JavaCast() approach? I am very curious to see if that works.
   
   Comment by Ben Nadel – June 23, 2010
7. @David, @Jason and @Ben - you all had me excited for a moment! I tried to use Javacast but it throws the error:
   
   JavaCast type coldfusion.sql.QueryTable must be one of the following types: byte, char, short, int, long, float, double, boolean, string, bigdecimal, their corresponding array representation (eg : int[]), or null.
   
   Comment by John Whish – June 23, 2010
8. @John,
   
   I wasn't sure the JavaCast function would work. I remembered the error messages it's given me when I type integer instead of int. Java lets you do something called "Upcasting" www.codeguru.com/java/tij/tij0070.shtml />
   Just not sure how we'd manage this in Coldfusion.
   
   Comment by David Boyer – June 23, 2010
9. Well I got the class to return coldfusion.sql.QueryTable but it still didn't want to work in a QoQ
   
   
   
   artists = EntityToQuery(EntityLoad("ARTISTS"));
   WriteOutput('Artists: ' & artists.getClass().getName());
   WriteDump(artists);
   
   artists2 = artists.getShallowCopy();
   WriteOutput('Artists2: ' & artists2.getClass().getName());
   WriteDump(artists2);
   
   Comment by David Boyer – June 23, 2010
10. I see my first mistake, I misread the doc.
    
    help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7fbe.html />
    The indenting was screwed up on these list items
    
    -xxx[]?where xxx is one of the following:
    -any of the preceding types, except for null
    -a Java class name
    
    All I saw was "A java class name"
    
    So if it were going to work, you'd have to ask for an array of said items. But it seems it only works for simple types, i.e. int[] String[]. So I don't think you'd be able to do "coldfusion.sql.QueryTable[]"
    
    Later in the docs, I found this:
    
    "JavaCast cannot be used to cast between complex objects, nor to cast to a super-class."
    
    Comment by Jason Dean – June 23, 2010
11. I am surprised the JavaCast() didn't work. I could be totally wrong, but I swear I remember doing an actual Class cast for something a while back. The documentation says that one of the "types" can be a "a Java class name". Or course, it also says, "Use only for scalar, string, and array arguments."
    
    Comment by Ben Nadel – June 23, 2010
12. I tried a few things using the .getClass().cast() function but couldn't get it working. It'd either throw an error complaining I was doing something daft or would appear to work and not change the class.
    
    getShallowCopy() did turn it into the right type but I think it's missing some extra metadata that QoQ's need.
    
    Comment by David Boyer – June 23, 2010
13. Wow.. quite a discussion here.. You can not use javacast to cast it to non-primitive types. Javacast can only typecaste to primitive types, null or array. For non-primitive type objects, you would never need to do any typecasting as ColdFusion will always take care of that.
    
    Coming to the problem with EntityQueryTable, it has some metadata missing that is required for it to work in QoQ. And AFAIK, there is no workaround for this and you will have to wait for the updater.
    
    Comment by Rupesh Kumar – June 23, 2010
14. If someone really needs this to work now, you can simply loop over the entityToQuery() result object and create a new query object with queryNew(), right? It's annoying to loop over a query to create a query so you can run a query of query, yes :P But if you need it as a workaround, it's one very quick UDF.
    
    Comment by Jamie Krug – June 23, 2010
15. What I ended up doing was using the the ArrayMax and ArrayMin functions for my particular use case as although there was some other data I wanted it seemed overkill to loop over and create a new query object when it will be included in 9.01.
    
    If anyone is interested to get the max and min costs I did:
    
    prices = ListToArray( ValueList( myQuery.sellingcost ) );
    maxprice = ArrayMax( prices );
    minprice = ArrayMin( prices );
    
    Comment by John Whish – June 23, 2010
16. Or why not execute hql directly?
    That will be much more neat and more efficient than doing all this.
    
    You can simply use
    ORMExecuteQuery(SELECT Max(p.sellingcost) as highestCost, Min(p.sellingcost) as LowestCost
    FROM Product)
    
    Comment by Rupesh Kumar – June 23, 2010
17. oops.. the hql needs to be in quoted above..
    
    ORMExecuteQuery("SELECT Max(p.sellingcost) as highestCost, Min(p.sellingcost) as LowestCost
    FROM Product as p")
    
    Comment by Rupesh Kumar – June 23, 2010
18. Thanks Rupesh,
    
    I'm a big fan of HQL and that's what I'm using to get the products. BTW I like using "select new map( Max(p.sellingcost) as highestCost, Min(p.sellingcost) as LowestCost ) FROM Product as p" so that I don't get an array of arrays back.
    
    There were two reasons why I didn't do that in this instance. Firstly, the products array is filtered by a lot params being passed in (category / brand / price range etc) so I didn't really want to have to have maintain and pass in the same arguments. The second reason was that I was trying to avoid hitting the database.
    
    Comment by John Whish – June 23, 2010
19. This has now been fixed in CF 9.0.1, which was released today.
    
    Comment by Bob Silverberg – July 14, 2010
20. I am hearing *awesome* things about CF 9.0.1. When will the weekend get here so I can play??
    
    Comment by Ben Nadel – July 14, 2010
21. John,
    
    Thanks for this post. Its saved me twice.
    
    Curt
    
    Comment by Curt Gratz – December 30, 2010