StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>[UPDATE]</strong> - <em>added (yet another) explanation on <code>for</code> comprehensions</em></p> <ol> <li><p>The <code>*</code> method:</p> <p>This returns the <em>default projection</em> - which is how you describe:</p> <blockquote> <p>'all the columns (or computed values) I am <em>usually</em> interested' in.</p> </blockquote> <p>Your table could have several fields; you only need a subset for your default projection. The default projection must match the type parameters of the table.</p> <p>Let's take it one at a time. Without the <code><></code> stuff, just the <code>*</code>:</p> <pre><code>// First take: Only the Table Defintion, no case class: object Bars extends Table[(Int, String)]("bar") { def id = column[Int]("id", O.PrimaryKey, O.AutoInc) def name = column[String]("name") def * = id ~ name // Note: Just a simple projection, not using .? etc } // Note that the case class 'Bar' is not to be found. This is // an example without it (with only the table definition) </code></pre> <p>Just a table definition like that will let you make queries like:</p> <pre><code>implicit val session: Session = // ... a db session obtained from somewhere // A simple select-all: val result = Query(Bars).list // result is a List[(Int, String)] </code></pre> <p>the default projection of <code>(Int, String)</code> leads to a <code>List[(Int, String)]</code> for simple queries such as these.</p> <pre><code>// SELECT b.name, 1 FROM bars b WHERE b.id = 42; val q = for (b <- Bars if b.id === 42) yield (b.name ~ 1) // yield (b.name, 1) // this is also allowed: // tuples are lifted to the equivalent projection. </code></pre> <p>What's the type of <code>q</code>? It is a <code>Query</code> with the projection <code>(String, Int)</code>. When invoked, it returns a <code>List</code> of <code>(String, Int)</code> tuples as per the projection.</p> <pre><code> val result: List[(String, Int)] = q.list </code></pre> <p>In this case, you have defined the projection you want in the <code>yield</code> clause of the <code>for</code> comprehension. </p></li> <li><p>Now about <code><></code> and <code>Bar.unapply</code>. </p> <p>This provides what are called <em>Mapped Projections</em>.</p> <p>So far we've seen how slick allows you to express queries in Scala that return a <em>projection of columns</em> (or computed values); So when executing these queries <em>you have to think of the result row</em> of a query <strong>as a Scala tuple</strong>. The type of the tuple will match the Projection that is defined (by your <code>for</code> comprehension as in the previous example, of by the default <code>*</code> projection). This is why <code>field1 ~ field2</code> returns a projection of <code>Projection2[A, B]</code> where <code>A</code> is the type of <code>field1</code> and <code>B</code> is the type of <code>field2</code>.</p> <pre><code>q.list.map { case (name, n) => // do something with name:String and n:Int } Queury(Bars).list.map { case (id, name) => // do something with id:Int and name:String } </code></pre> <p>We're dealing with tuples, which may be cumbersome if we have too many columns. We'd like to think of results not as <code>TupleN</code> but rather some object with named fields.</p> <pre><code>(id ~ name) // A projection // Assuming you have a Bar case class: case class Bar(id: Int, name: String) // For now, using a plain Int instead // of Option[Int] - for simplicity (id ~ name <> (Bar, Bar.unapply _)) // A MAPPED projection // Which lets you do: Query(Bars).list.map ( b.name ) // instead of // Query(Bars).list.map { case (_, name) => name } // Note that I use list.map instead of mapResult just for explanation's sake. </code></pre> <p>How does this work? <code><></code> takes a projection <code>Projection2[Int, String]</code> and returns a mapped projection on the type <code>Bar</code>. The two arguments <code>Bar, Bar.unapply _</code> tell slick how this <code>(Int, String)</code> projection must be mapped to a case class.</p> <p>This is a two-way mapping; <code>Bar</code> is the case class constructor, so that's the information needed to go from <code>(id: Int, name: String)</code> to a <code>Bar</code>. And <code>unapply</code> if you've guessed it, is for the reverse.</p> <p>Where does <code>unapply</code> come from? This is a standard Scala method available for any ordinary case class - just defining <code>Bar</code> gives you a <code>Bar.unapply</code> which is an <strong>extractor</strong> that can be used to get back the <code>id</code> and <code>name</code> that the <code>Bar</code> was built with:</p> <pre><code>val bar1 = Bar(1, "one") // later val Bar(id, name) = bar1 // id will be an Int bound to 1, // name a String bound to "one" // Or in pattern matching val bars: List[Bar] = // gotten from somewhere val barNames = bars.map { case Bar(_, name) => name } val x = Bar.unapply(bar1) // x is an Option[(String, Int)] </code></pre> <p>So your default projection can be mapped to the case class you most expect to use:</p> <pre><code>object Bars extends Table[Bar]("bar") { def id = column[Int]("id", O.PrimaryKey, O.AutoInc) def name = column[String]("name") def * = id ~ name <>(Bar, Bar.unapply _) } </code></pre> <p>Or you can even have it per-query:</p> <pre><code>case class Baz(name: String, num: Int) // SELECT b.name, 1 FROM bars b WHERE b.id = 42; val q1 = for (b <- Bars if b.id === 42) yield (b.name ~ 1 <> (Baz, Baz.unapply _)) </code></pre> <p>Here the type of <code>q1</code> is a <code>Query</code> with a <em>mapped</em> projection to <code>Baz</code>. When invoked, it returns a <code>List</code> of <code>Baz</code> objects:</p> <pre><code> val result: List[Baz] = q1.list </code></pre></li> <li><p>Finally, as an aside, the <code>.?</code> offers <strong>Option Lifting</strong> - the Scala way of dealing with values that may not be.</p> <pre><code> (id ~ name) // Projection2[Int, String] // this is just for illustration (id.? ~ name) // Projection2[Option[Int], String] </code></pre> <p>Which, wrapping up, will work nicely with your original definition of <code>Bar</code>:</p> <pre><code>case class Bar(id: Option[Int] = None, name: String) // SELECT b.id, b.name FROM bars b WHERE b.id = 42; val q0 = for (b <- Bars if b.id === 42) yield (b.id.? ~ b.name <> (Bar, Bar.unapply _)) q0.list // returns a List[Bar] </code></pre></li> <li><p>In response to the comment on how Slick uses <code>for</code> comprehensions:</p> <p>Somehow, monads always manage to show up and demand to be part of the explanation...</p> <p>For comprehensions are not specific to collections only. They may be used on any kind of <em>Monad</em>, and collections are just one of the many kinds of monad types available in Scala.</p> <p>But as collections are familiar, they make a good starting point for an explanation:</p> <pre><code>val ns = 1 to 100 toList; // Lists for familiarity val result = for { i <- ns if i*i % 2 == 0 } yield (i*i) // result is a List[Int], List(4, 16, 36, ...) </code></pre> <p>In Scala, a for comprehension is syntactic sugar for method (possibly nested) method calls: The above code is (more or less) equivalent to:</p> <pre><code>ns.filter(i => i*i % 2 == 0).map(i => i*i) </code></pre> <p>Basically, anything with <code>filter</code>, <code>map</code>, <code>flatMap</code> methods (in other words, a <em>Monad</em>) can be used in a <code>for</code> comprehension in place of <code>ns</code>. A good example is the <a href="http://www.scala-lang.org/api/current/scala/Option.html" rel="nofollow noreferrer">Option monad</a>. Here's the previous example where the same <code>for</code> statement works on both the <code>List</code> as well as <code>Option</code> monads:</p> <pre><code>// (1) val result = for { i <- ns // ns is a List monad i2 <- Some(i*i) // Some(i*i) is Option if i2 % 2 == 0 // filter } yield i2 // Slightly more contrived example: def evenSqr(n: Int) = { // return the square of a number val sqr = n*n // only when the square is even if (sqr % 2 == 0) Some (sqr) else None } // (2) result = for { i <- ns i2 <- evenSqr(i) // i2 may/maynot be defined for i! } yield i2 </code></pre> <p>In the last example, the transformation would perhaps look like this:</p> <pre><code>// 1st example val result = ns.flatMap(i => Some(i*i)).filter(i2 => i2 %2 ==0) // Or for the 2nd example result = ns.flatMap(i => evenSqr(i)) </code></pre> <p>In Slick, queries are monadic - they are just objects with the <code>map</code>, <code>flatMap</code> and <code>filter</code> methods. So the <code>for</code> comprehension (shown in the explanation of the <code>*</code> method) just translates to:</p> <pre><code>val q = Query(Bars).filter(b => b.id === 42).map(b => b.name ~ 1) // Type of q is Query[(String, Int)] val r: List[(String, Int)] = q.list // Actually run the query </code></pre> <p>As you can see, <code>flatMap</code>, <code>map</code> and <code>filter</code> are used to generate a <code>Query</code> by the repeated transformation of <code>Query(Bars)</code> with each invocation of <code>filter</code> and <code>map</code>. In the case of collections these methods actually iterate and filter the collection but in Slick they are used to generate SQL. More details here: <a href="https://stackoverflow.com/questions/13758445/how-does-scala-slick-translate-scala-code-into-jdbc/13758728#13758728">How does Scala Slick translate Scala code into JDBC?</a></p></li> </ol>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload