Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to define optional foreign key in Slick?

Tags:

scala

slick

I have a table like this:

object Addresses extends Table[AddressRow]("address") {
   def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def street = column[String]("street")
  def number = column[String]("number")
  def zipcode = column[String]("zipcode")
  def city = column[String]("city")
  def country = column[String]("country")
  def geoLocationId = column[Int]("geo_location_id", O.Nullable)

 // Foreign keys.
 def geoLocation = foreignKey("fk_geo_location", geoLocationId, GeoLocations)(_.id)

 // Rest of my code.
 ...
}

where my case class is:

case class AddressRow(
  id: Option[Int] = None,
  street: String,
  number: String,
  zipcode: String,
  city: String,
  country: String,
  geoLocationId: Option[Int])

As you notice geoLocation is an optional foreign key....

I can't find any way to describe this "Optional" in my foreign key definition.

I've tried like:

  def geoLocation = foreignKey("fk_geo_location", geoLocationId.asColumnOf[Option[Int]], GeoLocations)(_.id)

but I receive:

Caused by: scala.slick.SlickException: Cannot use column Apply Function Cast in foreign key constraint (only named columns are allowed)

Does anybody has a suggestion?

like image 670
Cristian Boariu Avatar asked Mar 12 '13 21:03

Cristian Boariu


2 Answers

Try The following:

def geoLocationId = column[Option[Int]]("geo_location_id")
//Foreign Key
def geoLocation = foreignKey("fk_geo_location", geoLocationId, GeoLocations)(_.id.?)

geoLocationId is now a Column of Option[Int] therefore O.Nullable is no longer needed (_.id.?) returns the GeoLocation as an option, or None if it was null.

like image 127
Wellingr Avatar answered Oct 10 '22 07:10

Wellingr


I don't think that what you are trying to do is accomplishable by using foreign keys. Check out joining and user defined types from the Slick docs.

Note the example with the leftJoin:

val explicitLeftOuterJoin = for {
  (c, s) <- Coffees leftJoin Suppliers on (_.supID === _.id)
} yield (c.name, s.name.?)

So if you wanted to query for all of your Addresses, you'd want to start with something like

val addressGeolocQuery = for {
  (addr, loc) <- Addresses leftJoin GeoLocations on (_.geoLocationId === _.id)
} yield addr.id ~ loc.prop1.? ~ loc.prop2.? /*and so on*/

You could then map the results of that query so that you get back an actual Address instance, complete with an Option[GeoLocation]. That's why I linked the "user defined types" in the docs... that's a new feature to me (I was familiar with ScalaQuery, which was Slick's previous incarnation), but it looks fairly promising.

like image 3
Dylan Avatar answered Oct 10 '22 06:10

Dylan