N1QL ORDER BY parameterized


#1

Hello,
I am trying to use $params in an ORDER BY clause as follows:
ORDER BY $orderParam ASC, then in the java code I am creating the query like so:
return N1qlQuery.parameterized(queryGetCustomerByFilter, namedParams);
namedParams is a JsonObject which contains all of my named properties with their values.

When I run this query I can see that the ordering fails which leads me to believe that the $orderParam is not being properly substituted by its value.
Can someone provide any pointers as to why this is happening?

Also I would like to know if its possible to externalize the ASC|DESC somehow and leave some kind of placeholder like so: ORDER BY $orderParam $order

Thanks for the help!


#2

Parameters are values not fields or expressions. If those are values why do you need ORDER BY because those are constants will not change for each document. So ORDER BY expressions and order type are required during query optimization time at that time those are not available. So those can’t be parameterized.

You can create multiple prepare statements and execute them.

If you want supply filed names as parameters Try this

insert into default values("k2",{"a":1}), ("k3",{"a":10});
select * from default AS d ORDER BY d.[$a1] DESC;

NOTE: order by expression d.[$a1] set $a1 to “a”. ASC|DESC can’t be parameterized


#3

The reason why I am doing this is because I am loading a full query from a properties file in Java, said query already has all of the named parameters placeholders as $paramName.
This query I am using so that I can pass a wide variety of values that will be interpreted by the query or ignored, using what you gave me before: IFMISSINGORNULL
Now, almost everything in this query is dynamic, meaning there are no hardcoded values, every value is passed as a named parameter when building the query:
return N1qlQuery.parameterized(queryGetCustomerByFilter, namedParams);
where is the query I load from the properties file, and namedParams is a JsonObject contatining all of the named parameters, names and values.
The only part left from the query to make dynamic is the sorting, so I was hopping to be able to externalize that ORDER BY as a named parameter, so that I could pass at runtime a parameter and have the query be run with that parameter name for sorting, same as the sorting order (ASC|DESC) which I would also like to externalize, I could edit the query string before I pass it to the N1qlQuery.parameterized(…) method and concatenate the query string with " ORDER BY " + myParamPassedAtRuntime + " " + " ASC “, or " ORDER BY " + myParamPassedAtRuntime + " " + " DESC” and then pass this new string to the parameterized method and I am sure it will work, however I find this not to be ideal, I was hoping for some native N1QL solution.


#4

You can also use above suggested approach .