Relations and Foreign Keys in Squeryl

Question

Im using Scala, Squeryl and MySql to build a web app.

I found it easy to persist simple data as strings or integers. But what about when i have relations between objects and i need to use foreign keys. In my app i have areas, and sub areas which have an attribute of type Area (Area where they belong) so my Area and Subarea are like these

   class Area(
            var idArea: String,

            @BeanProperty
            var name:String,
            @BeanProperty
            var letter: String,
            @BeanProperty
            var color: String
            )
  extends Idable {
  def this() = this("","", "","")

}

class SubArea(var idSubArea: String,
              @BeanProperty
              var name: String,

              @BeanProperty
              var area:Area

               ) extends Idable {

  def this() = this("","",null )

How do i define the schema, so my SubArea table has an Area id, foreign key to my Area Table?? For the time being my SubArea schema is like these

object SubAreaSchema extends Schema {

  val subAreas=table[SubArea]
  on(subAreas)(subArea => declare(
    subArea.id is (autoIncremented),
    subArea.name is (unique)
  ))
}

Answer 1

You can define the relation in your schema with:

val areaToSubAreas = oneToManyRelation(areas, subAreas).via((a,sA) => s.idArea === sa.areaId) 

To make that work, you would want to modify your SubArea class load the foreign key's id directly, as below with the areaId:String.

class SubArea(var idSubArea: String,
              @BeanProperty
              var name: String,

              @BeanProperty
              var areaId: String)

and then in the method body, if you want to have access to the object, you can use:

 def area = areaToSubAreas.right(this) 

which will yield an ManyToOne[Area] which you query, or use headOption on to convert to an Option[Area].

Conversely, if you need to reference the subareas on Area, you can use:

 def subAreas = areaToSubAreas.left(this) 

which will yield an OneToMany[Area] which can be iterated over, or you can also call toList.