Search code examples
scalaplayframeworkplayframework-2.0anorm

anorm dynamic filters

I'm studying a little the anorm documentation (from play framework) and is not clear if it supports a common query use case: dynamic filters, that is the user fills in 2 or 3 search criteria on a 10 fields search form.

In this case how can I dynamically construct the query without the classic string manipulation?

Solution

  • Yes, I think the question referenced by Robin Green contains the answer. Just define your query with all the possible criteria using placeholders (e.g. {criterion1}) and call the on() method on the query, passing the actual Seq of Option parameters as described in the accepted answer.

    Modified example from the Anorm doc, assuming you have two criteria but only want your query to filter on the country code and not on the capital:

    SQL(
"""
select * from Country c 
join CountryLanguage l on l.CountryCode = c.Code 
where ({countryCode} is null or c.code = {countryCode})
  and ({capital} is null or c.capital = {capital});
"""
).on("countryCode" -> Some("FRA"), "capital" -> None)

    That should do the trick.