Filter Calendar Events by Query String and WHERE clause

Siavash Mortazavi asked on July 31, 2018 15:25

Hi, I have some pages of type Booking Event for calendar, and I have a repeater to render them. I would like to be able to filter out the events by adding "fromdate" and "todate" query strings to the URL, in a way that the repeater only fetches items with start date in that range.

Here is how I've filled the "WHERE" field of the repeater: {% QueryString.fromdate != "" ? "EventDate >='" + SQLEscape(QueryString.fromdate) + "'" : "" + QueryString.todate != "" ? " and EventDate <='" + SQLEscape(QueryString.todate) + "'" : "" #%}

It's supposed to build a T-SQL WHERE clause like this (if the query string parameters are available): WHERE eventdate >='2018-8-1' and eventdate <='2018-9-20'

This doesn't work, but still adding that where clause with the syntax above to a direct SELECT over the table works fine.

Anyone knows the issue? I guess it's related to how I'm concatenating two parts of the clause, maybe '+' doesn't work in macro expressions?! Thanks.

Correct Answer

Siavash Mortazavi answered on August 1, 2018 03:28

OK, I finally found it, I just wrapped each section with parentheses and also added the missing conditional 'and'. Now, this covers all 4 possible scenarios: {% (QueryString.fromdate != "" ? "EventDate >= '" + SQLEscape(QueryString.fromdate) + "'" : "") + ((QueryString.fromdate != "" && QueryString.todate != "") ? " and " : "") + (QueryString.todate != "" ? "EventDate <= '" + SQLEscape(QueryString.todate) + "'" : "") |(identity)GlobalAdministrator%}

0 votesVote for this answer Unmark Correct answer

Recent Answers

Peter Mogilnitski answered on July 31, 2018 18:32 (last edited on December 10, 2019 02:31)

you have a mistake in your macro, I guess it should be something like:

{% (QueryString.fromdate != "" && QueryString.todate != "")? "EventDate >='" + SQLEscape(QueryString.fromdate) and EventDate <='" + SQLEscape(QueryString.todate) + "'" : "" |(identity)GlobalAdministrator%}

0 votesVote for this answer Mark as a Correct answer

Siavash Mortazavi answered on August 1, 2018 03:18 (last edited on December 10, 2019 02:31)

Thanks Peter, actually the correct syntax you're thinking of is: {% (QueryString.fromdate != "" && QueryString.todate != "")? "EventDate >= '" + SQLEscape(QueryString.fromdate) + "' and EventDate <= '" + SQLEscape(QueryString.todate) + "'" : "" |(identity)GlobalAdministrator%}

The problem with this logic is that it requires both "fromdate" and "todate" to be present, but I would like my macro to work with either of the parameters or both (I know to achieve this I'll need to add the and conditionally too). My macro already works with "fromdate" only, but ignores "todate" section. So, can you point to the part of my macro that is incorrect?

The logic I'm looking for is something like this: 1. if "fromdate" is provided, concatenate the "fromdate" section. 2. If both "fromdate" and "todate" are presented, concatenate ' and ' 3. If "todate" is present, concatenate the todate section.

Thanks again.

0 votesVote for this answer Mark as a Correct answer