3

I have a query object, with, say, fifteen rows returned. For all intents and purposes, I can't modify the SQL which generates the query object, but I need to sort this query object by column. Is there a way to do this in ColdFusion 7 without resorting to an external library?

Edit: I should add: I've run a query on this query object, and done an ORDER BY clause in this query of the query. Is there another way of doing this?

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
Hooray Im Helping
  • 5,194
  • 4
  • 30
  • 43
  • Regarding: "Is there another way of doing this?" - Can you tell why QoQ is not sufficient in this case? – Tomalak Jul 22 '09 at 17:29
  • Tomalak: Due to some extenuating circumstances, the would happen in a view page, which hurts my soul way more than a function call would. – Hooray Im Helping Jul 22 '09 at 17:40
  • 2
    Yeah, I hate to do that myself. Could you not create a UDF to wrap the QofQ? Then you would just call the function in your view. Less of a sin, I'd think. – ale Jul 22 '09 at 18:34
  • FWIW Giancarlo Gomez referred to this thread in a [blog post](http://www.giancarlogomez.com/2010/10/re-sort-existing-query-without-qoq-in.html). – Sebastian Zartner Feb 27 '14 at 08:09

3 Answers3

14

No, a query of query is the way you would do this. There are other ways you could monkey around with the data, but they're all kludgey and wouldn't be as straightforward as QoQ.

One of the powers of QoQ (aka in-memory queries) is that it can be used on the query returned by any tag that returns a query object, for instance CFDIRECTORY and CFPOP.

For folks wondering how to do a Query of Query, it's simple:

<cfquery name="resortQuery" dbtype="query">
    SELECT *
    FROM myQueryFromSomewhereElse
    WHERE
        COLUMN_NAME=<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#myFilterValue#" />
    ORDER BY
        SOME_OTHER_COLUMN_NAME ASC
</cfquery>
rrk
  • 15,677
  • 4
  • 29
  • 45
ale
  • 6,369
  • 7
  • 55
  • 65
12

Even when this question is already solved I want to add, that you could also use the underlying Java method sort(), which is just needing one line and you don't need to add a UDF for this. The code would then look like this:

<cfset qQuery.sort(qQuery.findColumn("nameOfSortColumn"), TRUE)>

The findColumn() call is needed to get the index of the sort column, because sort() is working with the column indexes and not with the column names. The second parameter specifies the sort order: TRUE = ascending, FALSE = descending.

Advantages: one-line call, faster than QoQ

Disadvantage: sort restricted to one column

There are much more hidden features of the underlying Java class. See the following links for more information:

In Lucee (tested with 4.5 and 5.2) this also works similar, and even simpler:

<cfset qQuery.sort("nameOfSortColumn", "asc")>

Hope, this gives some ideas...

Sebastian Zartner
  • 18,808
  • 10
  • 90
  • 132
  • Is there any info on up-version capabilities to CF9-10 and so on? Does this has any performance drawbacks or advantages? – rrk Jun 11 '14 at 09:31
  • 1
    As far as I know this should also work up to CF 10. This should be faster than a query of query as the query has to be interpreted first. Unfortunately I can't test both at the moment to give more precise information on that. – Sebastian Zartner Jun 11 '14 at 15:01
  • @SebastianZartner thanks for the tip, but unfortunately I'm not able to make it work. `qryAddresses.sort(1, TRUE)` returns this error: "key [1] not found in query, columns are [addressID,memberID,createDate,lastUpdated,addressName,...]. Any suggestions? – thdoan Mar 24 '15 at 09:52
  • P.S. I'm using CF9, so now I'm thinking it's not supported because `qryAddresses.getColumnCount()` returns "No matching Method/Function for Query.getColumnCount() found"; however, `qryAddresses.findColumn()` _is_ supported. – thdoan Mar 24 '15 at 09:59
  • 2
    This no longer works as of ColdFusion 2016 - the sort() function has been reimplemented with a single parameter (a comparator function). https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-m-r/querysort.html#main-pars_header – Juffy Dec 19 '17 at 08:23
0

Please check the next url

http://www.coldfusioncookbook.com/entries/How-do-I-resort-a-query.html

<cfquery name="original" datasource="foo" >
  select name, age, rank
  from people
  order by age asc
</cfquery>

<!--- Resort by name --->
<cfquery name="newQuery" dbtype="query">
  select name, age, rank
  from original
  order by name asc
</cfquery>
Peter Boughton
  • 110,170
  • 32
  • 120
  • 176
mvergel
  • 19
  • 1