Conditonally UPDATE fields with Slick String interpolation

Question

I have two Options:

val name: Option[String] = ...
val shared: Option[Boolean] = ...

I would like to build an UPDATE query that SETs these fields if the above values are Some, but leaves them unchanged if they are None.

I have managed to achieve this like so, but I am not so keen on the check for the comma, which is not going to scale if I need to add extra columns. I'm also not so keen on having to use a var.

 var query = Q.u + "UPDATE wishlist SET"
 if(name.isDefined)  query = query + " name = " +? name + (if (share.isDefined) ", " else "")
 if(shared.isDefined) query = query + " shared = " +? share

If I wasn't concerned with SQL injection, I could do something along the lines of:

 val fields = List(
   name.map(n => s"name = '$n'"),
   shared.map(e => s"shared = $e")
 ).flatten

 s"UPDATE wishlist SET ${fields.mkString(", ")}"

Question: Is there a nicer way to do this with Slick's plain SQL/String interpolation or is my only option to move to lifted embedded?

cmbaxter cmbaxter · Accepted Answer · 2013-08-09T17:52:58

It's not super elegant, but I think it's at least flexible in that it will expand to support many inputs w/o changing the underlying logic:

 val name:Option[String] = Some("foo")
 val share:Option[String] = Some("bar")

 val sets = List(
   name.map(n => (" name = ", n)),
   share.map(s => (" shared = ", s))
 ).flatten

 val query2 = sets.zipWithIndex.foldLeft(Q.u + "UPDATE wishlist SET"){
   case (q,(p,0)) => q + p._1 +? p._2
   case (q,(p,i)) => q + "," + p._1 +? p._2
 }

I'm putting the field name and value pairs into a list as Options for Tuples and then flattening to remove the Nones. I'm then folding to produce the final statement, taking into account that any piece of the query past the first piece will need a comma in it.

Conditonally UPDATE fields with Slick String interpolation

1 Answers