1

I am getting error when trying to execute the below code:

myQuery = queryNew("id,name,amount","Integer,Varchar,Integer", 
            [ 
                    {id=1,name="One",amount=15}, 
                    {id=2,name="Two",amount=18}, 
                    {id=3,name="Three",amount=32},
                    {id=4,name="Four",amount=27},
                    {id=5,name="Five",amount=43},
                    {id=6,name="Six",amount=71}
            ]);

get=myQuery.filter(function(obj){
   return (order by obj.name )
});
writeOutput("The filtered query is:")
writeDump(get);

The error displays "Invalid CFML construct found on line 16 at column 22."

I know the above error is because of order by obj.name as a parameter in return. But how will I be able to do without any error? Thank you.

S M
  • 159
  • 2
  • 13
  • 1
    Are you sure you don't need `querySort` instead? https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-m-r/querysort.html – Cory Fail Nov 28 '18 at 20:18
  • A `filter()` should only return a boolean value to decide whether your variable should contain the row from the query. – Shawn Nov 28 '18 at 20:20

1 Answers1

3

To do an order by, you're going to need to do sort() and not filter().

<cfscript>
myQuery = queryNew("id,name,amount","Integer,Varchar,Integer", 
            [ 
                    {id=1,name="One",amount=15}, 
                    {id=2,name="Two",amount=18}, 
                    {id=3,name="Three",amount=32},
                    {id=4,name="Four",amount=27},
                    {id=5,name="Five",amount=43},
                    {id=6,name="Six",amount=71}
            ]);

myQuery.sort("name", "desc");
writeOutput("The filtered query is:")
writeDump(myQuery);
</cfscript>

https://trycf.com/gist/f827f61a03a173a3b13ad01a2cd13dce/lucee5?theme=monokai

Edited: There is no need to reference get, just reference the query itself.

Cory Fail
  • 1,070
  • 8
  • 26
  • Yes it is correct. it is automatically sorting in ascending but how to sort in descending instead. Thanks – S M Nov 28 '18 at 20:27
  • 1
    `get = myQuery.sort("name", "desc");` – Cory Fail Nov 28 '18 at 20:28
  • Thank you so much @fyroc – S M Nov 28 '18 at 20:30
  • is there anyway in the same code, would we be able to get the values of column for name only without getting all the columns in a query result? Thank you – S M Nov 28 '18 at 20:41
  • 1
    If the query already exists and being loaded what's the benefit of getting only those columns? You could do `ValueList(get.name)` for a list of records from that column. `ValueArray()` is also now available. – Cory Fail Nov 28 '18 at 20:48
  • oh ok. @fyroc. I got it what you mean. Yeah I was just trying to optimize a bit so there is not point of it then. Thank you so much. – S M Nov 28 '18 at 20:50
  • You'll only be able to optimize it in the `SELECT` – Cory Fail Nov 28 '18 at 20:52
  • 1
    @SM Are you saying that your base query has a bunch of columns you don't need? – Shawn Nov 28 '18 at 21:03
  • If that is the case QoQ might be a better option than just sorting it. – Cory Fail Nov 28 '18 at 21:11
  • @fyroc Watch out for QoQ. It can easily become a lot of unneeded overhead. – Shawn Nov 29 '18 at 00:47