Re: Object hierarchy on a single link table.

From: Andrus Adamchik (
Date: Tue May 11 2004 - 12:51:08 EDT

  • Next message: Hill, Michael S CONT: "RE: Object hierarchy on a single link table."

    Hi Mike,

    Since relationships return *all* related objects, you can't have a
    relationship implicitly qualified on User. From your schema ChargeItem
    can potentially have many users. Even if this is not the case in real
    life, DB schema allows that.

    The best option that I see is to manually create a method in ChargeItem
    class that returns all roles for a given user. E.g.:

    public List rolesForUser(User user) {
        Expression qualifier = ...
        SelectQuery query = new SelectQuery(Role.class, qualifier);
        return getDataContext().performQuery(query);


    On May 11, 2004, at 11:04 AM, Hill, Michael S CONT wrote:

    > 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.description,, t0.lead,
    > t0.location_loa,, 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 - 12:51:13 EDT