Possible solution to the InterestRateTermValueToFinancingTerm problem

Solution.scala

package com.typesafe.slick.examples.lifted

// Use H2Driver to connect to an H2 database
import scala.slick.driver.H2Driver.simple._

// Use the implicit threadLocalSession
import Database.threadLocalSession

/**
 * A simple example that uses statically typed queries against an in-memory
 * H2 database. The example data comes from Oracle's JDBC tutorial at
 * http://download.oracle.com/javase/tutorial/jdbc/basics/tables.html.
 */
object FirstExample extends App {

  // Definition of the SUPPLIERS table
  object Suppliers extends Table[(Int, String, String, String, String, String)]("SUPPLIERS") {
    def id = column[Int]("SUP_ID", O.PrimaryKey) // This is the primary key column
    def name = column[String]("SUP_NAME")
    def street = column[String]("STREET")
    def city = column[String]("CITY")
    def state = column[String]("STATE")
    def zip = column[String]("ZIP")
    // Every table needs a * projection with the same type as the table's type parameter
    def * = id ~ name ~ street ~ city ~ state ~ zip
  }

  // Definition of the COFFEES table
  object Coffees extends Table[(String, Int, Double, Int, Int)]("COFFEES") {
    def name = column[String]("COF_NAME", O.PrimaryKey)
    def supID = column[Int]("SUP_ID")
    def price = column[Double]("PRICE")
    def sales = column[Int]("SALES")
    def total = column[Int]("TOTAL")
    def * = name ~ supID ~ price ~ sales ~ total
    // A reified foreign key relation that can be navigated to create a join
    def supplier = foreignKey("SUP_FK", supID, Suppliers)(_.id)
  }

  object SupplierToCountry extends Table[(Int, String)]("SUPPLIER_TO_COUNTRY") {
    def supplier_id = column[Int]("SUP_ID")
    def country = column[String]("COUNTRY")

    def * = supplier_id ~ country
  }

  // Connect to the database and execute the following block within a session
  Database.forURL("jdbc:h2:mem:test1", driver = "org.h2.Driver") withSession {
    // The session is never named explicitly. It is bound to the current
    // thread as the threadLocalSession that we imported

    // Create the tables, including primary and foreign keys
    (Suppliers.ddl ++ Coffees.ddl ++ SupplierToCountry.ddl).create

    // Insert some suppliers
    Suppliers.insert(101, "Acme, Inc.",      "99 Market Street", "Groundsville", "CA", "95199")
    Suppliers.insert( 49, "Superior Coffee", "1 Party Place",    "Mendocino",    "CA", "95460")
    Suppliers.insert(150, "The High Ground", "100 Coffee Lane",  "Meadows",      "CA", "93966")

    //Insert SupplierToCountry links
    SupplierToCountry.insert(101, "Norway")
    SupplierToCountry.insert(101, "Sweden")
    SupplierToCountry.insert(49, "Sweden")
    SupplierToCountry.insert(49, "Denmark")
    SupplierToCountry.insert(150, "Denmark")

    // Insert some coffees (using JDBC's batch insert feature, if supported by the DB)
    Coffees.insertAll(
      ("Colombian",         101, 7.99, 0, 0),
      ("French_Roast",       49, 8.99, 0, 0),
      ("Espresso",          150, 9.99, 0, 0),
      ("Colombian_Decaf",   101, 8.99, 0, 0),
      ("French_Roast_Decaf", 49, 9.99, 0, 0)
    )

    println("SupplierToCountry distribution")
    println("--------\n")
    Query(SupplierToCountry) foreach {case (id, country) =>
      println(" "+id+" - "+country)
    }

    val countriesToFind = Set("Norway", "Sweden")

    val q1 = for {
      (id, s) <- SupplierToCountry filter (_.country inSet countriesToFind) groupBy (_.supplier_id)
    } yield id -> s.map(_.country).countDistinct
    println("Finding ids by number of matches in the countriesToFind set")
    println("--------\n")
    println(q1._selectStatement)
    q1.list foreach {println(_)}

    val q2 = for {
      (id, _) <- q1.filter(_._2 === countriesToFind.size)
      s <- Suppliers if s.id === id
    } yield s
    println("\n Finding suppliers who's id matches the right number of times")
    println("--------\n")
    println(q2._selectStatement)
    q2.list foreach {println(_)}

    val q3 = for {
      (id, _) <- SupplierToCountry.
      filter(_.country inSet countriesToFind).
      groupBy(_.supplier_id).
      map { case (id, stc) => id -> stc.map(_.country).countDistinct }.
      filter (_._2 === countriesToFind.size)
      s <- Suppliers if s.id === id
    } yield s
    println("\n Putting it all together")
    println("--------\n")
    println(q3._selectStatement)
    q3.list foreach {println(_)}
    assert(q3.list.head._1 == 101)

  }
}