composite keys on many-to-many relationships

[Sparky]

Hi, I asked this question on CFTalk Andrew Scott did a good job at
answering, but i just need a little me help understanding, below is
the question. what I don't understand is that both the keys that ORM
uses in the link table is a filed type ID . all tables are created by
ORM and it creates the link table i don;t create the link table
entity .


"For any Composite Key in ORM you need to define them both in the FK
of your relationship, which you are not doing here." - could you
expand on this, to me this looks like I am doing that i.e.
fkcolumn="accID" etc



QUESTION.................................

I created a many-to-many relationship in ORM (see below). When I add
an account to a scape i.e. addAccount(account) the second time I add
the 'same' account to the scape I 'should' get an error because of the
composite key.

However, ORM happily adds it!

Scape.cfc

property name="Accounts" singularname="Account" fieldtype="many-to-
many" cfc="pAccount" fkcolumn="scapeID" inversejoincolumn="accID"
linktable="pLinkScapesAccount";

Account.cfc

property name="Scapes" singularname="Scape" fieldtype="many-to-many"
cfc="pScape" fkcolumn="accID" inversejoincolumn="scapeID"
linktable="pLinkScapesAccount";


What should I have done? There should never be two composite keys the
same in the link table so why does ORM let me do this? Is there a way
to force the correct behaviour? Thanks for you help

[Andrew Scott]

What I did say and I think you missed it, is that with your property you
need something like this in your entity. Never done this with a many-to-many
so I might be wrong, and I am sure someone else will correct me.

What you have

property name="Accounts" singularname="Account" fieldtype="many-to-many"
cfc="pAccount" fkcolumn="scapeID" inversejoincolumn="accID"
linktable="pLinkScapesAccount";

And what you should have.

property name="Accounts" singularname="Account" fieldtype="many-to-many"

cfc="pAccount" fkcolumn="scapeID,accID" inversejoincolumn="accID"
linktable="pLinkScapesAccount";

Now this is making the assumption that the composite key is scapeId and
accId, and the other property can be removed as this will and should create
the both of them. But you will also need to add another attribute
column="scapeId,accId"

Hopefully that might get you going a bit more. Also it may also pay I can't
recall if you added this in cf-talk or not, but could you give us the basics
of the entities, just the ID's and maybe one or two columns to help further.

Basically from the properyt I have given you above (and it is un tested) is
that it will map to the pAccount entity and use the fkcolumns of scapeId and
accId as the composite keys.

Regards,
Andrew Scott
http://www.andyscott.id.au/

> --
> You received this message because you are subscribed to the Google Groups
> "cf-orm-dev" group.
> To post to this group, send email to cf-or...@googlegroups.com.
> To unsubscribe from this group, send email to cf-orm-
> dev+uns...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/cf-
> orm-dev?hl=en.

[Sparky]

I see now, makes sense. Thank you so much for taking the time to
explain this to me, it really helped. Thanks again Andrew.

On Aug 10, 5:29 pm, "Andrew Scott" <andr...@andyscott.id.au> wrote:
> What I did say and I think you missed it, is that with your property you
> need something like this in your entity. Never done this with a many-to-many
> so I might be wrong, and I am sure someone else will correct me.
>
> What you have
>
> property name="Accounts" singularname="Account" fieldtype="many-to-many"
> cfc="pAccount" fkcolumn="scapeID" inversejoincolumn="accID"
> linktable="pLinkScapesAccount";
>
> And what you should have.
>
> property name="Accounts" singularname="Account" fieldtype="many-to-many"
> cfc="pAccount" fkcolumn="scapeID,accID" inversejoincolumn="accID"
> linktable="pLinkScapesAccount";
>
> Now this is making the assumption that the composite key is scapeId and
> accId, and the other property can be removed as this will and should create
> the both of them. But you will also need to add another attribute
> column="scapeId,accId"
>
> Hopefully that might get you going a bit more. Also it may also pay I can't
> recall if you added this in cf-talk or not, but could you give us the basics
> of the entities, just the ID's and maybe one or two columns to help further.
>
> Basically from the properyt I have given you above (and it is un tested) is
> that it will map to the pAccount entity and use the fkcolumns of scapeId and
> accId as the composite keys.
>
> Regards,

> Andrew Scotthttp://www.andyscott.id.au/

[Sparky]

Sometimes I surprise myself with just how stupid I can be.....

In a blog post I made sometime back I had this same issue. See here:
http://www.cfcoffee.co.uk/index.cfm/2010/9/20/Head-Spin-Moment-ORM-manytomany-Help

However at the time my outcome was that I could not force a compose
link-table with composite primary key in ORM if I did not add a 3rd
entity join table in place. Has anyone else done it this way?

[Andrew Scott]

Yeah it is one of those things if you;re not doing it often enough, its very easy to forget. I had troubles with an ORM issues just recently with another friend and although I had half the relationships working I was stumped on the other half of it.

And when you get to a point of well it should be working sometimes the obvious goers out the window.

Mind you we still haven't solved this issue, as what should work actually doesn't. But I am sure it is something that we both are over looking.

[Sparky]

Silly me, I can force it to work by added the 3rd entity as I bogged
about, but not using fkcolumn="scapeID,accID"

property name="Accounts" singularname="Account" fieldtype="many-to-
many"

cfc="pAccount" fkcolumn="scapeID,accID" inversejoincolumn="accID"
linktable="pLinkScapesAccount";

I added the column="scapeId,accId" but Hibernate gives an error I
recall "Foreign key must have same number of columns as the referenced
primary key" I found some interesting reads related to this:
http://www.coderbag.com/NHibernate/Foreign-key-must-have-same-number-of-columns-as-the-referenced-primary-key

This seems so straight forward I don't understand why I am having so
much of an issue, my heads not in the right place today.


here are my 2 entities before the change, still not resolved using the
FK without me having to create the link table as an entity.


Scape.cfc

component displayname="Scape Entity" entityname="pScape"
persistent="true" extends="baseEntity" accessors=true {

// Persistant Properties
property name="scapeID" length="100" type="string"
ormtype="string" fieldtype="id" generator="guid";
property name="scapeName" length="35" type="string"
ormtype="string" notnull="true" persistent=true default=""


// non persistent values

// Related Object Properties

property name="Accounts" singularname="Account" fieldtype="many-to-

many" cfc="pAccount" fkcolumn="scapeID,accID"
inversejoincolumn="accID" linktable="pLinkScapesAccount";

Account.cfc

component displayname="Account Entity" output="false" hint="I am the
core account object object. Other sub-classes will extend me to define
specific types of accounts."
persistent="true" discriminatorcolumn="accountType"
extends="baseEntity" table="pAccount" {


// Persistant Properties
property name="accID" length="100" type="string"
ormtype="string" fieldtype="id" generator="guid";
property name="accFName" length="35" type="string"
ormtype="string" notnull="true" persistent=true default=""
hint="";



// Related Object Properties

property name="Scapes" singularname="Scape" fieldtype="many-to-many"
cfc="pScape" fkcolumn="accID" inversejoincolumn="scapeID"
linktable="pLinkScapesAccount";

On Aug 11, 11:40 am, Andrew Scott <andr...@andyscott.id.au> wrote:
> Yeah it is one of those things if you;re not doing it often enough, its very
> easy to forget. I had troubles with an ORM issues just recently with another
> friend and although I had half the relationships working I was stumped on
> the other half of it.
>
> And when you get to a point of well it should be working sometimes the
> obvious goers out the window.
>
> Mind you we still haven't solved this issue, as what should work actually
> doesn't. But I am sure it is something that we both are over looking.
>
>
>
>
>
>
>
> On Thu, Aug 11, 2011 at 8:16 PM, Sparky <cfspa...@gmail.com> wrote:
> > Sometimes I surprise myself with just how stupid I can be.....
>
> > In a blog post I made sometime back I had this same issue. See here:
>

> >http://www.cfcoffee.co.uk/index.cfm/2010/9/20/Head-Spin-Moment-ORM-ma...