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).