Dynamic queries in Rogue

I’ve spent some time googling for information about how to build Rogue queries dynamically but surprisingly I couldn’t find any straightforward answer. This is why I created following short post describing how to do it.

An usual query may look like this:

UserRecord where (_.surname eqs "Gates") and (_.age eqs 20) limit(5) fetch()

What if we want to add the our criteria conditionally? My first attempt looked naively like this:

def loadUser(searchedNameOpt: Option[String], searchedAgeOpt: Option[Int]) = {
val getAllQuery = UserRecord
val queryByName = searchedNameOpt map(searchedName => getAllQuery where (_.name eqs searchedName)).getOrElse(getAllQuery)
val finalQuery = searchedAgeOpt map(searchedAge => queryByName and(_.age eqs searchedAge)).getOrElse(queryByName)
finalQuery.orderDesc(_.age).limit(5).fetch()
}
view raw WrongQuery.scala hosted with ❤ by GitHub

It turns out you can’t compile this code, because type of queryByName is lost. The map().getOrElse() expression returns one of two different types. What you need here is to add ‘query‘ invocation to your record:
def loadUser(searchedNameOpt: Option[String], searchedAgeOpt: Option[Int]) = {
val getAllQuery = (UserRecord limit 5 orderDesc (_.age)).query
val queryByName = searchedNameOpt map(searchedName => getAllQuery where (_.name eqs searchedName)) getOrElse getAllQuery
val finalQuery = searchedAgeOpt map(searchedAge => queryByName and(_.age eqs searchedAge)) getOrElse queryByName
finalQuery fetch()
}

That’s pretty much it, somehow it is not easy to find in any examples. The queryByName object can now be used as a base for elegant, dynamic and typesafe Rogue query.  Also, note that orderDesc() and limit() are as well pulled to the first line. Special thanks to Piotr Buda, who showed me his queries in Slick which inspired me to go this way.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s