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.

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(orderBy : String) = orderBy match {
  case "id" => Query.orderBy(baseQuery.value.id)>>baseQuery
  case "name" => Query.orderBy(baseQuery.value.name)>>baseQuery
  case "color" => Query.orderBy(baseQuery.value.color)>>baseQuery
  case "price" => Query.orderBy(baseQuery.value.price)>>baseQuery
  case _ => baseQuery
}

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. (baseQuery.value._x)

def order(orderBy : String) = orderBy match {
  case "id" => Query.orderBy(query.value._1)>>query
  case "name" => Query.orderBy(query.value._2)>>query
  case "color" => Query.orderBy(query.value._3)>>query
  case _ => baseQuery
}

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)