Defining new queries

April 2, 2012 · By Christian Ready · 8 Comments

When you need to create a query object, it can be a bit complicated to create one with several columns and rows. Take a look at the following example:

<cfset qry = queryNew("Firstname,Name,Zip,Country")>
<cfset queryAddRow(3)>
<cfset querySetCell(qry, "Firstname", "Gert", 1)>
<cfset querySetCell(qry, "Name", "Franz", 1)>
<cfset querySetCell(qry, "Country", "Switzerland", 1)>
<cfset querySetCell(qry, "Firstname", "Mark", 2)>
<cfset querySetCell(qry, "Name", "Drew", 2)>
<cfset querySetCell(qry, "Country", "United Kingdom", 2)>
<cfset querySetCell(qry, "Firstname", "Todd", 3)>
<cfset querySetCell(qry, "Name", "Rafferty", 3)>
<cfset querySetCell(qry, "Country", "USA", 3)>

Now compare that to the following notation available in Railo:

<cfset qry = query(	firstname:["Gert","Mark","Todd"],
			name:["Franz","Drew","Rafferty"],
			country:["Switzerland","United Kingdom","USA"])>

Tags: HowTo · Tips

8 responses so far ↓

  • 1 Marco Spescha // Apr 2, 2012 at 4:30 PM

    Thats a lot better.
    For me a notation like this would make much more sense:
    query([{firstname:"Gert",name:"Franz"},{"firstname:"Mark",name:"Drew"}]);

    So you can have a function getNameStruct() returning a struct, that you can append to an array and convert it to a query.

    a query is kind of an array of structs, isn't it?
  • 2 Michael Offner // Apr 2, 2012 at 5:21 PM

    @Marco
    Your example makes more "noise". you have to write the column name again and again.

    a query is a struct of columns (in simple terms) ;-)
    see this example:
    #query.column[2]#
    in this example i get the column from the query and from the column the row.
  • 3 Scott Stroz // Apr 2, 2012 at 5:31 PM

    Very nice.
  • 4 Nando // Apr 4, 2012 at 11:10 AM

    Well, the "natural" way to add data is by row, and the new notation adds data by column.
    What about:

    qry = queryNew("Firstname,Name,Country")
    querySetRow(qry, "Gert", "Franz", "Switzerland")
  • 5 Nando // Apr 4, 2012 at 12:02 PM

    Or how about this, a slight difference that includes the possibility to easily set nulls:

    qry = queryNew("Firstname,Name,Zip,Country");
    querySetRow(qry, "Gert", "Franz", null, "Switzerland");

    In looping constructs where I've used QuerySetCell(), I've only set values by row. I'm not sure I'd run across cases very often where setting values by column would make sense, but maybe that's just my own limitation as a programmer!
  • 6 Kyle // Apr 6, 2012 at 8:41 PM

    You forgot an Array opening bracket, there, mate
  • 7 Christian Ready // Apr 6, 2012 at 9:05 PM

    Good catch, and corrected. Thanks for keeping me honest :)
  • 8 Dawesi // Aug 11, 2014 at 5:55 AM

    Would be great if we could add to queries easily...

    queryAddRow(query,[{row:1, data:''},{row:2,data:'bla'}])

Leave a Comment

Leave this field empty: