CODEWITHSUNDEEP
databasescalaslickslick-3.0
Elaqqad
Elaqqad

Reputation: 85

create a class Table for an inexisting table slick scala (Slick 3.0.0, scala)

Assume that we have a database which contains two tables: Coffee and Suppliers and we have their corresponding case classes and tables, just as in the documentation: 

import scala.slick.driver.MySQLDriver.simple._
import scala.slick.lifted.{ProvenShape, ForeignKeyQuery}


// A Suppliers table with 6 columns: id, name, street, city, state, zip
class Suppliers(tag: Tag) extends Table[(Int, String, String, String, String, String)](tag, "SUPPLIERS") {
  def id: Column[Int] = column[Int]("SUP_ID", O.PrimaryKey) // This is the primary key column
  def name: Column[String] = column[String]("SUP_NAME")
  def street: Column[String] = column[String]("STREET")
  def city: Column[String] = column[String]("CITY")
  def state: Column[String] = column[String]("STATE")
  def zip: Column[String] = column[String]("ZIP")

  // Every table needs a * projection with the same type as the table's type parameter
  def * : ProvenShape[(Int, String, String, String, String, String)] = (id, name, street, city, state, zip)
}

// A Coffees table with 5 columns: name, supplier id, price, sales, total
class Coffees(tag: Tag) extends Table[(String, Int, Double, Int, Int)](tag, "COFFEES") {
  def name: Column[String] = column[String]("COF_NAME", O.PrimaryKey)
  def supID: Column[Int] = column[Int]("SUP_ID")
  def price: Column[Double] = column[Double]("PRICE")
  def sales: Column[Int] = column[Int]("SALES")
  def total: Column[Int] = column[Int]("TOTAL")

  def * : ProvenShape[(String, Int, Double, Int, Int)] = (name, supID, price, sales, total)

  // A reified foreign key relation that can be navigated to create a join
  def supplier: ForeignKeyQuery[Suppliers, (Int, String, String, String, String, String)] = 
    foreignKey("SUP_FK", supID, TableQuery[Suppliers])(_.id)
}

Now assume that we want to do a join:

   val result = for {
       c <- coffees
       s <- suppliers if c.supID === s.id
} yield (c.name, s.name)

And here dealing with the result is complicated ( and it's more complicated if we have a lot of joins) because we need always to remember the order of names, to know what _._1 or _._2 refer to ... etc.

Question 1 Is there a way to change the type of the result as a table of a new class which contains the desired columns ?

Question 2 Here is a way but I can't finish it, we construct a case class for example:

case class Joined(nameS: String,nameC: String)

and after that we construct the corresponding table which I don't know how 

class Joineds extends Table[Joinedclass] {
//Todo
}

and when we write a join we can write something like ( so that we can transform result to a type Joined) :

   val result = for {
       c <- coffees
       s <- suppliers if c.supID === s.id
} yield (c.name, s.name).as(Joinds)

Thank you.

Upvotes: 0

Views: 661

Answers (1)

Rikard
Rikard

Reputation: 692

Can you define it like:

val result = for {
  c <- coffees
  s <- suppliers if c.supID === s.id
} yield Joined(c.name, s.name)

And the tuck it away in some convenient place?

Upvotes: 1

Related Questions