In the previous section, you learned about the different invokers and how they mapped to SQL statements. We brushed over the methods supported by columns themselves, however: we can compare for equality using ===
, but what other operations are supported by Slick columns?
Most of the SQL functions are supported. For instance, to get the total donations to candidates whose name starts with "O"
, we could run the following:
scala> db.withSession { implicit session => Tables.transactions.filter { _.candidate.startsWith("O") }.take(5).list } List[Tables.Transactions#TableElementType] = List(Transaction(Some(1594098)...
Similarly, to count donations that happened between January 1, 2011 and February 1, 2011, we can use the .between
method on the date
column:
scala> val dateParser = new SimpleDateFormat("dd-MM-yyyy") dateParser: java.text.SimpleDateFormat = SimpleDateFormat scala> val startDate = new java.sql.Date(dateParser.parse("01-01-2011").getTime()) startDate: java.sql.Date = 2011-01-01 scala> val endDate = new java.sql.Date(dateParser.parse("01-02-2011").getTime()) endDate: java.sql.Date = 2011-02-01 scala> db.withSession { implicit session => Tables.transactions.filter { _.date.between(startDate, endDate) }.length.run } Int = 9772
The equivalent of the SQL IN (...)
operator that selects values in a specific set is inSet
. For instance, to select all transactions to Barack Obama and Mitt Romney, we can use the following:
scala> val candidateList = List("Obama, Barack", "Romney, Mitt") candidateList: List[String] = List(Obama, Barack, Romney, Mitt) scala> val donationCents = db.withSession { implicit session => Tables.transactions.filter { _.candidate.inSet(candidateList) }.map { _.amount }.sum.run } donationCents: Option[Long] = Some(2874484657) scala> val donationDollars = donationCents.map { _ / 100 } donationDollars: Option[Long] = Some(28744846)
So, between them, Mitt Romney and Barack Obama received over 28 million dollars in registered donations.
We can also negate a Boolean column with the !
operator. For instance, to calculate the total amount of donations received by all candidates apart from Barack Obama and Mitt Romney:
scala> db.withSession { implicit session => Tables.transactions.filter { ! _.candidate.inSet(candidateList) }.map { _.amount }.sum.run }.map { _ / 100 } Option[Long] = Some(1930747)
Column operations are added by implicit conversion on the base Column
instances. For a full list of methods available on String columns, consult the API documentation for the StringColumnExtensionMethods
class (http://slick.typesafe.com/doc/2.1.0/api/#scala.slick.lifted.StringColumnExtensionMethods). For the methods available on Boolean columns, consult the API documentation for the BooleanColumnExtensionMethods
class (http://slick.typesafe.com/doc/2.1.0/api/#scala.slick.lifted.BooleanColumnExtensionMethods). For the methods available on numeric columns, consult the API documentation for NumericColumnExtensionMethods
(http://slick.typesafe.com/doc/2.1.0/api/#scala.slick.lifted.NumericColumnExtensionMethods).