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)
  ))
}

score 2 · Accepted Answer · answered Nov 01 '12 at 19:52

2

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.

answered Nov 01 '12 at 19:52

jcern

7,798
4
39
47

thanks for your answer. I try adding to my schema the OneToMany Relation. And adding this code in my classes In Area: def subAreasObj={ FollowUpDB.areaToSubAreas.left(this) } And in subArea: def subAreasObj={ FollowUpDB.areaToSubAreas.left(this) } When I call this: var prueba:SubArea=subAreaDao.findSubArea(2) var prueba2:Area=prueba.areaObj.head This error: Failed to Boot! Your application may not run properly java.lang.RuntimeException: no session is bound to current thread, a session must be created via Session.create and bound to the thread via 'work' or 'bindToCurrentThread' – Guillermo Zooby Nov 06 '12 at 17:49
I am not sure where you are calling that from, but the error is that no transaction is started (often happens in threads or at boot). If you wrap the call with: inTransaction{ prueba.areaObj.head }, you will join an existing transaction or start a new one if necessary. See: http://squeryl.org/sessions-and-tx.html – jcern Nov 06 '12 at 18:46
Thanks for your answer. Yes im calling it from Boot, just to print something to see if it works. Now I tried what you said, i wrap the call in inTransaction, but the same error is appearing... – Guillermo Zooby Nov 06 '12 at 19:34
I am not sure what the test code block looks like, but you will also need to make sure that the findSubArea call is wrapped as well. Anything that accesses the database is going to need a database transaction to execute against. You can wrap all your test logic in the same block: inTransaction{ var prueba:SubArea=subAreaDao.findSubArea(2); var prueba2:Area=prueba.areaObj.head; println(prueba2) } – jcern Nov 06 '12 at 20:05
Now its working fine, i had to call it in a method wrap with Transaction. Thank you for your help @jcern – Guillermo Zooby Nov 06 '12 at 20:09
Now I want to try a ManyToMany relation, i have a student that has a list of courses, and a course, has a list of students, so I already defined a join table in my schema, and the following relation: val coursesStudents = manyToManyRelation(courses, students). via[StudentCourse]((c,s,sc) => (sc.idStudent === s.id, c.id === sc.idCourse)) So now, so how can i access that list?? how can i do it similar to the other relations? Thanks @jcern – Guillermo Zooby Nov 07 '12 at 20:30
the ManyToMany has the same left/right methods, so you could define a method in Student as coursesStudents.right(this) and/or in Course as coursesStudents.left(this). The toList method will yield a list in both cases. – jcern Nov 08 '12 at 15:01

Relations and Foreign Keys in Squeryl

1 Answers1

Linked