Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Slick- use foreignKey constraint and access referenced object directly as column

I have two models (say, Supplier and Coffee) and Coffee model has foreign key reference to Supplier model. During ddl, I want this relationship to exist in table creation. But I also want to be able to refer the Supplier object through Coffee object like coffeeObj.supplier.name. Below is my dummy code. I am using MappedTable, foreignKey and TypeMapper.

import scala.slick.driver.H2Driver.simple._
import Database.threadLocalSession

object SlickTest {
  // Supplier
  case class Supplier(id: Option[Int], name: String)
  object Suppliers extends Table[Supplier]("supplier") {
    def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
    def name = column[String]("name")
    def * = id.? ~ name <> (Supplier, Supplier.unapply _)
    def findOneById(id: Int): Supplier = 
      this.map { e => e }.where(r => r.id === id).firstOption.get
  }

  // Implicit Conversion from Supplier to Int
  implicit val supTypeMapper = MappedTypeMapper.base[Supplier, Int](
    { s => s.id.get }, { id => Suppliers.findOneById(id) })

  // Coffee
  case class Coffee(name: Option[String], sup: Supplier, price: Double)
  object Coffees extends Table[Coffee]("coffee") {
    def name = column[String]("cof_name", O.PrimaryKey)
    def sup = column[Supplier]("supplier")
    def price = column[Double]("price")
    def * = name.? ~ sup ~ price <> (Coffee, Coffee.unapply _)

    def supplier = foreignKey("SUP_FK", sup, Suppliers)(_.id)
  }
}

The code is failing at the last line for the definition of supplier. Could anyone shed any light?

like image 527
Khalid Saifullah Avatar asked Oct 17 '13 12:10

Khalid Saifullah


People also ask

Is it possible to reference one column as multiple foreign keys?

A single column can have multiple foreign key constraints.

Do foreign keys have to be the same type?

When you're using a foreign key to reference a column in another table, the datatypes of both tables have to be the same. For example, if the referencing column where you're declaring the foreign key uses the INT data type, then the referenced column must be INT as well.

What is foreign key violation?

Foreign key constraint violation occurred, dbname = <database_name> , table name = <table_name> , constraint name = <constraint_name> . 23000. Occurs when an insert or update on a foreign key table is performed without a matching value in the primary key table.

What is foreign key constraint error?

The error message itself showing there is a foreign key constraint error, which means you are deleting a parent table where the child table contains the Primary table identifier as a foreign key. To avoid this error, you need to delete child table records first and after that the parent table record.


1 Answers

In Slick you don't map to case classes that reference other case classes. To resolve foreign keys you use queries instead, which you can put into methods for re-usability.

Also see my post at: https://groups.google.com/d/msg/scalaquery/esFb2DjoHVE/NtMj7BmpE94J

EDIT: You can't follow a reference in coffeeObj and that is a good thing. Because that would require configuring a loading strategy like in ORMs, which would be more complicated and would make the execution behavior of your code less obvious.

Lazy loading would probably load the coffeeObj in the controller and the supplier in the view, which seems odd, right? You can do this:

Remove .firstOption.get from your findOneById method and then:

val supplierQuery = Query(Suppliers).findById(id)
val coffeeQuery = supplierQuery.join(Coffee).on(...).map(_._2)
// here is what you want, a supplier and the corresponding coffee:
val supplierWithCoffee = (supplierQuery.firstOption,coffeeQuery.f​irstOption)

Put the join into a function to save boiler plate.

like image 184
cvogt Avatar answered Sep 22 '22 21:09

cvogt