Many-to-many relationship: how specify a composite foreign key?

[jules2008]

Hi

I am new to Grails and would be grateful if anybody would tell me whether the following is possible.

I am using Grails 1.0.1 and working with a legacy database.  I have a many-to-many relationship between two tables 'formats' and 'signatures'; both these tables have a composite primary key (format_id format_installation_id, and signature_id sig_installation_id, respectively) . They are linked through a join table 'format_signature_link' which has the fields:
format_id format_installation_id signature_id sig_installation_id.  

I have created the following domain classes but am unsure how to specify the foreign key references as I get the following error:

Compilation error: org.codehaus.groovy.control.MultipleCompilationErrorsExceptio
n: startup failed, C:\grails_applications\compositeId\grails-app\domain\Format.g
roovy: 9: expecting '}', found 'format_signature_link' @ line 9, column 73.
   atInstallationId"], joinTable 'format_si                                 ^

class Format implements Serializable{

  static hasMany = [signatures:Signature]

  static mapping={
    version false
        table 'formats'
        columns{
           signatures composite:["formatId","formatInstallationId"], joinTable 'format_signature_link'
        }
        id composite: ["formatId","formatInstallationId"]
  }
 
  Integer fileFormatId
  Integer fileInstallationId
 
  String formatNameText
  .
  .
  .
}

class Signature implements Serializable{

   static hasMany = [formats:Format]
   static belongsTo = Format

    static mapping = {
            version false
                table 'signatures'
                id composite:["signatureId", "sigInstallationId"]
                columns{
                  formats composite:["signatureId", "sigInstallationId"], joinTable:'format_signature_link'
                }
               
        }

        Integer sigInstallationId
        Integer signatureId
       
        String noteText
        .
        .
}


I have managed to get this working for a many-to-many relationship between two tables which have a single field primary key but am stuck with this scenario.  Any help would be greatly appreciated.  

Many thanks

Julie

[Leif Singer-2]

Hello,

maybe it's just the colon missing after 'joinTable'?
   Leif

> class Format implements Serializable{
>
>  static hasMany = [signatures:Signature]
>
>  static mapping={
>    version false
> table 'formats'
> columns{
>   signatures composite:["formatId","formatInstallationId"],  
> joinTableCOLON
> 'format_signature_link'
> }
> id composite: ["formatId","formatInstallationId"]
>  }

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[sergiomarcio2001]

Hey,

Actuallu I have this same problem once. In this case , Graemer told me that this is not possible yet. So, now you have to try map this kind of stuff by hibernate custom mapping. Look on hibernate documentation how to do this.

I my solution here I did another id increment field , and in my data base i put a constraint to composite fields.

So, that`s my advices about that!

Gook luck and I really apreciate if you get solution and share with us!


Cheers!
Sérgio Teixeira.

Leif Singer-2 wrote

Hello,

maybe it's just the colon missing after 'joinTable'?
   Leif

> class Format implements Serializable{
>
>  static hasMany = [signatures:Signature]
>
>  static mapping={
>    version false
> table 'formats'
> columns{
>   signatures composite:["formatId","formatInstallationId"],  
> joinTableCOLON
> 'format_signature_link'
> }
> id composite: ["formatId","formatInstallationId"]
>  }


---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[jules2008]

sergiomarcio2001 wrote

Hey,

Actuallu I have this same problem once. In this case , Graemer told me that this is not possible yet. So, now you have to try map this kind of stuff by hibernate custom mapping. Look on hibernate documentation how to do this.

I my solution here I did another id increment field , and in my data base i put a constraint to composite fields.

So, that`s my advices about that!

Gook luck and I really apreciate if you get solution and share with us!


Cheers!
Sérgio Teixeira.

Leif Singer-2 wrote

Hello,

maybe it's just the colon missing after 'joinTable'?
   Leif

> class Format implements Serializable{
>
>  static hasMany = [signatures:Signature]
>
>  static mapping={
>    version false
> table 'formats'
> columns{
>   signatures composite:["formatId","formatInstallationId"],  
> joinTableCOLON
> 'format_signature_link'
> }
> id composite: ["formatId","formatInstallationId"]
>  }


---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

Thanks for the advice.  I cannot believe that I did not spot the missing colon.  Anyway changed that and now get the error:

on: Error creating bean with name 'sessionFactory': Invocation of init method fa
iled; nested exception is org.hibernate.MappingException: Foreign key (FKB1272C6
9CACA9576:format_signature_link [formats_id])) must have same number of columns
as the referenced primary key (signatures [signature_id,sig_installation_id]):
org.hibernate.MappingException: Foreign key (FKB1272C69CACA9576:format_signature
_link [formats_id])) must have same number of columns as the referenced primary
key (signatures [signature_id,sig_installation_id])

As you suggest Sérgio I'll probably just add another ID field

Julie