TL;DR :where_clause: place holder is ignored in GetFeatureInfo requests leading to syntax error “:” but works fine in GetMap requests.
I have an SQL view layer with a basic SELECT query and a couple CASE conditions for columns. The problem I had was the fact that when using cql_filter the query being generated was like so;
SELECT ST_Simplify()
FROM (
SELECT (CASE WHEN END) as column
FROM table
)
WHERE cql_filter
Which because of CASE statement caused processing of the entire table first before being filtered.
I then added WHERE 1 = 1 :where_clause: like explained in the docs which fixed the issue for GetMap requests and moved the WHERE query inside the FROM sub query so that data was filtered first before being processed. Like so;
SELECT ST_Simplify()
FROM (
SELECT (CASE WHEN END) as column
FROM table
WHERE cql_filter
)
WHERE TRUE
However now when we send the same request to GetFeatureInfo it ignores :where_clause: and throws a syntax error “:”;
SELECT ST_Simplify()
FROM (
SELECT (CASE WHEN END) as column
FROM table
WHERE :where_clause:
)
WHERE cql_filter