a query like:
query = "SELECT user_id FROM users WHERE lang = %s ORDER BY CASE WHEN %s <> NULL THEN location <-> %s ELSE RANDOM() END LIMIT 1"
query(query, lang, location, location)
could be written as (using python):
location = ...
order = "RANDOM()" if location is None else "location <-> %s"
order_args = [] if location is None else [location]
query = "SELECT user_id FROM users WHERE lang = %s ORDER BY "+order+" LIMIT 1"
query(query, lang, *order_args)
why should be used in this case a "CASE" sql, when it's possible to do something like this, safe from sql injection and keeping the query very clean? Are there some pro and contros?
In case there are much more CASE in the query it could be a cleaner solution.
Building queries dynamically is a sound strategy, as long as you're careful about SQL injection. However it's usually easier to let the database engine do the work. The database engine may be able to cache queries and do other helpful things for you, depending on the specific scenario.
The only time you must use dynamic queries is when the variable part is something the database engine won't treat as variable, like schema and object names, or the presence / absence of an entire clause (ORDER BY, LIMIT and so on). The rest of the time, it's personal taste / judgment.