Modifying queries

A common task is to write a general query and to make modifications on it depending on some user input.

For instance if you’d like to create a query to populate a data grid allowing the user to sort on any of the columns in the grid.

To modify an existing query we can use the >> operator. When you want to add restrictions, sorting or grouping on already defined queries you need to recover the needed Columns from the existing query to create the addition.

Given the following table definition:

object Fruit extends Table[(Int, String, String, Float)]("Fruit"){
	def id = column[Int]("id", O.NotNull, O.AutoInc, O.PrimaryKey)
	def name = column[String]("name", O.NotNull)
	def color = column[String]("color")
	def price = column[Float]("price") 
	def * = id ~ name ~ color ~ price
}

Next we define a basic query:

val baseQuery = for (u<- Fruit) yield u

As the type of baseQuery is com.novocode.squery.combinator.Query[object Fruit] we can access the fields using their names. (baseQuery.value.id)

def order(q : Query[Fruit.type], orderBy : String) = orderBy match {
  case "id" => Query.orderBy(q.value.id) >> q
  case "name" => Query.orderBy(q.value.name) >> q
  case "color" => Query.orderBy(q.value.color) >> q
  case "price" => Query.orderBy(q.value.price) >> q
  case _ => q
}

Given the following query:

val query = for (u<- Fruit) yield u.id ~ u.name ~ u.color

As the type of query is com.novocode.squery.combinator.Query[com.novocode.squery.combinator.Projection3[Int,String,String]] we will now have to access the fields using their tuple index. (query.value._x)

def order(q : Query[Projection3[Int, String, String]], orderBy : String) = orderBy match {
  case "id" => Query.orderBy(q.value._1) >> q
  case "name" => Query.orderBy(q.value._2) >> q
  case "color" => Query.orderBy(q.value._3) >> q
  case _ => q
}

The same approach can be followed for groupBy.

(Query.groupBy{q.value.id}>>q).selectStatement

You could also do the same for with the where and having clause.

(Query.where{_ => q.value._2 like "A%"}>>q).selectStatement

But there is an easier way:

(q.where{_._2 like "A%"}).selectStatement

So why do we have recover the column from the initial query?

val query = for (u<- Fruit) yield u.id ~ u.name ~ u.color
(Query.orderBy(Fruit.id)>>query).selectStatement

generates the following SQL

SELECT t1.id,t1.name,t1.color FROM Fruit t1,Fruit t2 ORDER BY t2.id

This is done so that you’re able to write queries like this

val query = for (u1<- Fruit;
u2<-Fruit if (u1.id>u2.id)) yield u1.id ~ u2.id

SELECT t1.id,t2.id FROM Fruit t1,Fruit t2 WHERE (t1.id > t2.id)