Whatever message this page gives is out now! Go check it out!
ORMExecuteQuery(hql, params [,unique] [,queryOptions])
Last update:
Jun 9, 2026
This type of ORMExecuteQuery lets you pass unnamed parameters to the query. Use '?' (question mark) as the place-holder for the parameters. The values to the parameters should be passed as an array to params.Examples: unnamed parametersTo retrieve an array of artist objects with artistid equal to 40:
<cfset artists = ORMExecuteQuery("from ARTIST where artistid > ?", [40])>To retrieve an array of artwork objects with a priceid equal to 1:
<cfset artObj = ORMExecuteQuery("from ART where priceid=?", [1], true)>To retrieve an array of objects with a price id equal to 40, and price lesser than 80 dollars:
<cfset artists = ORMExecuteQuery("from ART where priceid > ? and price < ?", [40, 80])>Note:
In case of more than one parameter, values are picked up based on the parameter sequence, for example, the first parameter will be replaced by first value and second parameter will be replaced by second value.
Examples: named parametersThis type of ORMExecuteQuery lets you pass named parameters to the query. The placeholder for the parameter should be a name and should start with ":" as in ":age " or ":id ". The values to the names should be passed as key-value pairs.For example, to retrieve artist details of all artists whose reside in USA and are also citizens of USA, your code should look like this:
<cfset USArtists = ORMExecuteQuery("from ARTIST where country=:country and citizenship=:country", {country='USA'})>
<cfset orderDetail = ORMExecuteQuery("from Orders where OrderID=:orderid and ProductID=:productid", {orderid=1, productid=901}, true)>Note:
Parameters are not case-sensitive.
Examples: group byThis type of ORMExecuteQuery lets you retrieve aggregate or grouped values for the query. For example, to retrieve the first name and last name along with the status of the artwork being sold or not, you can write a query similar to the following:
<cfset artist = ORMExecuteQuery(
"SELECT art.Artist.Firstname, art.Artist.Lastname, SUM(art.Price) as Sold FROM Art as art WHERE art.IsSold=1 GROUP BY art.Artist.Firstname, art.Artist.Lastname")>
<cfloop array="#artist#" index="artistItem">
<cfoutput>
#artistItem[1]# #artistItem[2]# #artistItem[3]#<br>
</cfoutput>
</cfloop>Note:
Built-in functions to obtain the data such as getFirstName() or getLastName() cannot be used if you are using select queries with specific column names. The result will be returned as an array object and values can be retrieved using array index.
Example: order byThis type of ORMExecuteQuery lets you retrieve sorted data from a data source using the order by clause. For example, to sort the data from the Artist table by firstname , use the following code:
<cfset artist = ORMExecuteQuery('FROM Artist ORDER BY firstname ASC', false, {maxresults=5} )>
<cfloop array="#artist#" index="artistObj">
<cfoutput>Name = #artistObj.getFirstName()#
#artistObj.getLastName()#<br></cfoutput>
<br>
</cfloop>Example: aggregate functionsThis type of ORMExecuteQuery lets you retrieve data when using aggregate functions such as sum, count, avg.
<cfset artist = ORMExecuteQuery(
"SELECT COUNT(*) FROM Art as art WHERE art.Artist.ArtistID=:ArtistID AND art.IsSold=:Sold", { ArtistID=1, Sold=True }, True )>
<cfoutput>
#artist#
</cfoutput><br>Example: expressionsThis type of ORMExecuteQuery lets you retrieve data using expressions such as mathematical operators, logical operators, binary comparisons, and many others. For example, the following code is used to retrieve the price of an artwork, which is greater than or equal to 10000 along with the name and description of the artwork.
<cfset artArr = ORMExecuteQuery("from Art where price>=10000")>
<cfloop array="#artArr#" index="artObj">
<cfoutput>
Art Name = #artObj.getArtName()#<br>
Description = #artObj.getDescription()#<br>
Price = #artObj.getPrice()#<br>
</cfoutput>
<br>
</cfloop>
We're glad. Tell us how this page helped.
We're sorry. Can you tell us what didn't work for you?
Share additional feedback to help us improve.
0/255
|
