Query Data Source - dynamic ORDER BY expression?

John Ewan asked on September 1, 2015 23:36

I am very new to Kentico. I have created a Query data source that is selecting data from the View_CMS_User view. I would like to dynamically set the ORDER BY expression with something like this: {% if (QueryString["sort"] == "") {return "FileName ASC";} else {return QueryString["sort"] + " " + QueryString["dir"];} but this is not evaluated -- it is passed as-is to the WHERE clause.

Is this intentionally not supported in order to protect against injection attacks?
What alternatives do I have to support this requirement? The query data source is currently wired to a Basic Repeater to produce output.

Thanks for any help,

data source

Correct Answer

John Ewan answered on September 2, 2015 20:48

I solved the problem by (finding and) using a different repeater. The "repeater with custom query" web part allowed me to directly wire the query to the repeater and remove the data source. Then I was able to include my macro expression in the repeater's ORDER BY expression field and it was evaluated.

0 votesVote for this answer Unmark Correct answer

Recent Answers

Laura Frese answered on September 2, 2015 00:12 (last edited on December 10, 2019 02:30)

Maybe something in your logic is off just a tad. Which version of Kentico are you using? Try this:

{% if(QueryString.sort == "") { "FileName ASC"} else {QueryString.sort + " " + QueryString.dir} |(identity)GlobalAdministrator%}

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on September 2, 2015 05:06

I personally use QueryString.GetValue("sort"). Also ensure you're clicking on the little black left arrow when you enter a macro expression. If you simply enter it in the textbox, it may come back as plain text vs. the actual result of the macro expression. Also check out macro debugging.

0 votesVote for this answer Mark as a Correct answer

John Ewan answered on September 2, 2015 15:55

Thank you for the suggestions. I tried adding this:

{% if (QueryString.GetValue("sort") == "") {return "FullName ASC";} else {return QueryString.GetValue("sort") + " " + QueryString.GetValue("dir");}

in the pop-dialog that appears when you click the little black arrow but it is still not being evaluated -- it's going as-is to the WHERE clause.

Are there other ways I can accomplish this dynamic sorting against a custom SQL query?

For my current approach I will look at macro debugging in the meantime.

0 votesVote for this answer Mark as a Correct answer

Roman Hutnyk answered on September 2, 2015 16:30

Do you have order clause (##ORDERBY##) included into your custom query?

Kentico might evaluate macro you put into order by property of the web part, but custom query disregards it.

0 votesVote for this answer Mark as a Correct answer