5

Given a legacy database with a m:n relation and some additional attributes for the relation, how can this defined with squeryl. At the end the tables should look like this:


   +--------------+      +---------------+      +----------------+
   | TableA       |      | Rel_A_B       |      | TableB         |
   +--------------+ ____ +---------------+ ____ +----------------+
   | id: Int      |      | tableA: int   |      | compkey_1: int |
   | (more attrs) |      | tableB_1: int |      | compkey_2: int |
   +--------------+      | tableB_2: int |      | (more attrs)   |
                         | value: Varchar|      +----------------+
                         | date: Date    |
                         +---------------+

Theres no problem in defining the three tables manually with squeryl. However, as far as I understand the documentation at the moment (0.9.4) there is no possibility to define a many-to-many relationship with additional attributes for the relation.

That's why I defined three tables and two one-to-many relations:


// TableA
class TableA(val id: Int, ...) extends KeyedEntity[Int] {
    def this() = this(0, ...)
}

// TableB
class TableB(val compkey1: Int, val compkey2: Int, ...) 
        extends KeyedEntity[CompositeKey2[Int, Int]] {

    def id = CompositeKey2(compkey1, compkey2)
}

// Rel_A_B
class RelAB(val tabA: Int, val tabB1: Int, val tabB2: Int, val value: String, 
            val date: Date) extends KeyedEntity[CompositeKey3[Int, Int, Int]] {

    def id = CompositeKey3(tabA, tabB1, tabB2)
}

It's easy to define the relation between TableA and RelAB. I use an ordinary one-to-many relation:


val relA =
    oneToManyRelation(tableA, relAB).
    via((a, r) => a.id === r.tableA)

But I don't see a way to define the second relation. I already tried to define an additional composite value on the relation table (named compkeyB) containing just the columns from tableB and compare it to the composite key of tableB, but this doesn't work:


val relB =
    oneToManyRelation(tableB, relAB).
    via((b, r) => b.id === r.compkeyB)

It throws a "type mismatch" exception:

found   : org.squeryl.dsl.ast.LogicalBoolean
required: org.squeryl.dsl.ast.EqualityExpression

Any ideas how to solve this?

Steffen
  • 8,033
  • 1
  • 28
  • 34
  • Rather than (or better, in addition to) showing the code that works for the `tableA`/`relA` side of things, could you include the current broken code that's producing the exception? – Damien_The_Unbeliever Jul 04 '11 at 12:56
  • @Damien: I added the sample, it's basically the same as the working code but using CompositeKey2 vals, which obviously dosen't lead to an EqualityExpression. – Steffen Jul 04 '11 at 13:12
  • I recommend to post the question on the squeryl mailing list. – Christian Jul 05 '11 at 05:15

1 Answers1

6

You are being bitten by this limitation :

http://www.assembla.com/spaces/squeryl/tickets/25-compositekeys-cannot-be-the-binding-expression-for-relations

Max L.
  • 9,774
  • 15
  • 56
  • 86
  • Thanks for this answer, I already searched the open issues but obviously I overlooked this one. – Steffen Jul 05 '11 at 12:02
  • Are there any known workarounds yet? I have tables that are OneToMany related by two columns in each table. – Gabriel Mar 01 '13 at 15:20