3

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?

theon
  • 14,170
  • 5
  • 51
  • 74

1 Answers1

1

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.

cmbaxter
  • 35,283
  • 4
  • 86
  • 95
  • Thanks! That's definitely better. – theon Aug 09 '13 at 21:05
  • Turns out this doesn't work when using mixed types. This is because the `+?` method uses type classes. It uses the type of the argument to look up the associated `SetParameter` instance: `def +? [T](v: T)(implicit p: SetParameter[T])`. In this code if `name` is a `String` and `share` is a `Boolean`, then `sets` becomes a `List[(String,Any)]` and as you can imagine, calling `+?` with an `Any` won't work. As a workaround, I ended up writing a few lines of boilerplate to resolve the type class before `sets` gets created. My code now looks like [this](https://gist.github.com/theon/6215291) – theon Aug 12 '13 at 21:11