Object hierarchy on a single link table.

From: Hill, Michael S CONT (michael.hil..avy.mil)
Date: Tue May 11 2004 - 11:04:12 EDT

  • Next message: Budhiraja, Nikhil: "Enhancement request (Cayenne TRUE and FALSE expressions )"

    I am having a problem figuring out how to map this scenario via the modeler, and starting to wonder if it's possible.

    I have 3 tables that I need to create relationships on:

    1) user
    2) chargeitem
    3) role

    Each table has a primary key called pkey. I have a link (or join) table:

    role_assignment:
      user_id
      chargeitem_id
      role_id
      pkey (this table's primary key, just in case...)

    What I need is for the user object (User) to have a toMany relationship with Chargeitem, and then the Chargeitem to have a toMany relationship with Role, based on the User AND Chargeitem. What I get is:

    SELECT DISTINCT t0.active, t0.description, t0.global, t0.lead, t0.location_loa, t0.name, t0.organization_loa, t0.project_loa, t0.subsystem_loa, t0.team_loa, t0.pkey FROM role t0, role_assignment t1, chargeitem t2 WHERE t0.pkey = t1.role_id AND t1.chargeitem_id = t2.pkey AND (t2.pkey = '1')

    What I need is for that query to end with an AND that uses the users foreign key to narrow the query to just that User objects pkey.

    Any help would be greatly appreciated.

    Mike H.

    This archive was generated by hypermail 2.0.0 : Tue May 11 2004 - 11:04:47 EDT