Head Spin Moment ORM many-to-many Help?

I am trying to understand if this is correct way of looking at ORM and a many-to-many relationship.

Below I have two tables. Users and Monsters. A user can have many monsters, and a monster can have many users. Therefore I have a many-to-many relationship. Now I decide to include a join table called mostercollected to normalise the table somewhat. So below is what I come up with.

view plain print about
1/**
2* user
3*/
4component output="false" persistent="true" {
5
6property name="user_id" column="user_id" type="numeric"
7ormtype="int" fieldtype="id" generator="increment";
8
9property name="user_name" column="user_name"
10type="string" ormtype="string";
11    
12     /* link tables */    
13property name="monsters" fieldtype="many-to-many"
14CFC="monsters" FKColumn="user_id" singularname="monsters"
15inversejoincolumn="monster_id" linktable="monstersCollected";
16
17 users function init() output=false{
18 return this;
19 }
20
21}
22
23
24
25
26/**
27* monsters
28*/
29component output="false" persistent="true" {
30
31property name="monster_id" column="monster_id" type="numeric"
32ormtype="int" fieldtype="id" generator="increment";
33
34property name="monster_name" column="monster_name" type="string"
35ormtype="string" ;
36
37/* link tables */
38property name="users" fieldtype="many-to-many" CFC="users"
39FKColumn="monster_id" singularname="monsters"
40     inversejoincolumn="user_id" linktable="monstersCollected";
41
42 monsters function init() output=false{
43 return this;
44 }
45
46}
47
48
49 /* Join table */    
50
51
52
53component entityname="UserMonster" persistent="true" accessors="true"
54table="monstersCollected"
55{
56 property name="user_id"
57 fieldtype="id,many-to-one"
58 cfc="users"
59 cascade="all"
60 fkcolumn="user_id";
61
62 property name="monster_id"
63 fieldtype="id,many-to-one"
64 cfc="monsters"
65 cascade="all"
66 fkcolumn="monster_id";
67
68}

Now if I dump a user to screen, yes I can see all the monsters that each user has (cool), but the array continues down more, showing all monsters and all users that belong to that monster, effectively repeating data (bad, right?).

  • User1
    • Monster2
      • User1
      • User2
    • Monster1
      • User1
      • User2
  • User2
    • Monster2
      • User1
      • User2
    • Monster1
      • User1
      • User2

My concern is that of performance. If every time I want to get a user and I then get monsters + monster again and users that belong to that monster (head spin, moment), this seems like a lot of overhead when all I want is a list of the following...

  • User1
    • Monster2
    • Monster1
  • User2
    • Monster2
    • Monster1

Is this the issue I am making it out to be? Or have fundamentally missed the boat on this one and misunderstood completely?

Sep20

Post Info

Comments 3

[Add Comment] [Subscribe to Comments]
  1. Glyn Jackson's Gravatar # Posted By Glyn Jackson
    21/09/10 13:00

    The term is "lazy loading" so I have been told. Now makes sense!

  1. Dan Vega's Gravatar # Posted By Dan Vega
    04/10/10 18:09

    1st off you don't need 3 entities for the many to many relationship to work. If you create your User + Monster entities the linktable will be created, no need for a linktable entity.

    As far as lazy loading goes.. When a user entity is loaded entityLoadByPK("User",1) the collections by default are lazy loaded. This means that they are not actually loaded until they are needed. This is an obvious performance gain, we don't want to load the collection if we are not going to use it. A users monsters are not called until you

    user.getMonsters()

    of course this is the default, there are ways to change this behavior for certain situations where it would make sense to have them available. Read the docs for this, it should make sense.

  1. Glyn Jackson's Gravatar # Posted By Glyn Jackson
    04/10/10 19:16

    Hi Dan,

    Thanks for posting.

    From my understanding you cannot force a compose link-table with composite primary key in ORM if you dont have this 3rd enity in place. if I don't do this the other 2 enites would not know about each other. ORM lets me create duplicated keys in the linking table without any error without this 3rd table. I found this out my mistake. Not sure if it's the right way, but with the 3rd enity ORM acts correcty and will not allow me to create any duplicated keys. is this me or is there a better way to force this? Thanks :)

    could I ask, on the other point "lazy loading". If i dump out 'user', the mosters are all loaded as well, right? big performace issue for me, what if I just wanted to dump users. CF times out now when dumping users due to it also dumping montsers then users again that belong to that monster.

[Add Comment]