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
<!--- carry on and loop through products array and display... -->
I got the error:
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:
whereas a query returned from EntityToQuery has a java type of:
So, it does seem that you can't use Query of Query with EntityToQuery which is a real shame!