FAQ
I think I finally understand how I can create a many-to-many relationship
between objects in the modeler, but there are a lot of steps and some seem a
bit repetitive, so I want to ask if this is really the best / most
convenient way of doing it. In my example, I have Users and Exercises: users
perform a number of exercises, and exercises are performed by a number of
users, so we have a many to many. I would like to be able to just go to the
User object and add a many-to-many relation to Exercise, but as far as I can
tell i actually have to perform these steps (please correct me if I'm wrong;
i worked through these steps as I typed this so I know this at least works):

* Create a join table USER_EXERCISE to join the User pk and Exercise pk
* Create user_id and exercise_id attributes
* In the USER_EXERCISE table, create a to-one relationship to the User
pk, and a to-one relationship to the Exercise pk:
* Select the USER_EXERCISE entity
* Select the Relationships tab
* Click the Create Relationship button
* Name it "exerciseUsers"
* Select the Target to be the USER table
* Click the inspector button (first change focuse from Target
dropdown so that the inspector button is enabled)
* In the DbRelationship Info dialog:
* Name the Relationship "userExercises" and the Reverse Relationship
"exerciseUsers"
* Add a join by clicking the Add button
* For Source select exercise_id, and for Target select id (pk
from EXERCISE table)
* Repeate the same steps for the relation to the Exercise pk, naming the
relationship "userExercises"
* Create a flattened many-to-many relationship in the User object
* Select the User object (not the table)
* Go to the Relationships tab
* Click Create Relationship
* Click the Edit Relationship button. In the ObjRelationship Inspector
dialog that opens:
* Name the relationship "exercises"
* for Target select Exercise
* Under "Mapping to DbRelationships" section, select "exercises"
relationship, then the "userExercises" relationship (from the join table)
* Click "Select Path"
* Click "Done"
* Create a flattened many-to-many relationship in the Exercise object using
the same steps

Search Discussions

  • Robert Zeigler at Aug 25, 2011 at 8:16 pm
    Yup, that's about right. :)

    I never really thought about it being a lot of steps; I guess I always preferred having explicit control over how the join(s) work. But I see your point, and 90% of the time, a toMany uses a flattened relationship across a join table with no intermediate objects, so having this "default" behavior would be nice.

    Hibernate can auto-generate the join table. But it's always bugged me a little because it's been my experience that that it over-generate join tables (Eg: creating a join table for a one-to-many instead of associating the "to many" side with the fk of the "to one" side; you can do the latter, but it's way to easy to wind up with the former :).

    Still... what if we moved (or else duplicated?) the "toMany" checkbox in the join editor and had one next to each side of the relationship... and if both are selected, Cayenne would know you want a many-to-many and could potentially generate the join table and associated mapping information? ObjectEntity syncing also could be improved s.t. if both entities are around and you sync them, and there's no entity associated with the join table, and there are only pk columns in the join table, and there's no relationship already mapped between them, cayenne auto-generates the flattened relationship. The improvements above could potentially result in a workflow such as:

    * Create user table + attributes
    * Create exercise table + attributes
    * Create relationship in user table named "exercises" and map to "exercise" table; check "toMany"
    * Open up the relationship info dialog and put "users" into the reverse relationship and check "toMany"
    * Cayenne auto-generates the user_exercise table with pk exercise_id mapped to exercise.id (or whatever the Primary Key column is named in exercise) and pk user_id, and auto-adds the db relationship mapping, including adjusting the the target entity of the "exercises" relationship in the user table to be the join table.
    * Create the user object entity
    * Create the exercise object entity (toMany relationship to user added at this point because user entity exists)
    * sync user object entity with user table. toMany relationship to exercises added.

    The key thing with the entity syncing would be a set of rules along the lines of:
    Does Entity A have a toMany db relationship to another db entity (Entity B)?
    No? Stop
    Yes: Is entity B mapped as an object entity already?
    Yes? Stop
    No: Does entity B one and only one other attribute that is also a primary key?
    No? Stop
    Yes? Does entity B have a toOne db relationship that uses the 2nd primary key where the reverse relationship from entity C to entity B is toMany?
    No? Stop
    Yes: Generate a flattened relationship from A to C through the join table if it doesn't already exist.

    I definitely think this sort of improvement is worth having. Not sure whether the proposal outlined above is what we want. But I think it would cover the majority of cases.

    Robert

    On Aug 25, 2011, at 8/252:24 PM , Joel Becker wrote:

    I think I finally understand how I can create a many-to-many relationship
    between objects in the modeler, but there are a lot of steps and some seem a
    bit repetitive, so I want to ask if this is really the best / most
    convenient way of doing it. In my example, I have Users and Exercises: users
    perform a number of exercises, and exercises are performed by a number of
    users, so we have a many to many. I would like to be able to just go to the
    User object and add a many-to-many relation to Exercise, but as far as I can
    tell i actually have to perform these steps (please correct me if I'm wrong;
    i worked through these steps as I typed this so I know this at least works):

    * Create a join table USER_EXERCISE to join the User pk and Exercise pk
    * Create user_id and exercise_id attributes
    * In the USER_EXERCISE table, create a to-one relationship to the User
    pk, and a to-one relationship to the Exercise pk:
    * Select the USER_EXERCISE entity
    * Select the Relationships tab
    * Click the Create Relationship button
    * Name it "exerciseUsers"
    * Select the Target to be the USER table
    * Click the inspector button (first change focuse from Target
    dropdown so that the inspector button is enabled)
    * In the DbRelationship Info dialog:
    * Name the Relationship "userExercises" and the Reverse Relationship
    "exerciseUsers"
    * Add a join by clicking the Add button
    * For Source select exercise_id, and for Target select id (pk
    from EXERCISE table)
    * Repeate the same steps for the relation to the Exercise pk, naming the
    relationship "userExercises"
    * Create a flattened many-to-many relationship in the User object
    * Select the User object (not the table)
    * Go to the Relationships tab
    * Click Create Relationship
    * Click the Edit Relationship button. In the ObjRelationship Inspector
    dialog that opens:
    * Name the relationship "exercises"
    * for Target select Exercise
    * Under "Mapping to DbRelationships" section, select "exercises"
    relationship, then the "userExercises" relationship (from the join table)
    * Click "Select Path"
    * Click "Done"
    * Create a flattened many-to-many relationship in the Exercise object using
    the same steps
  • Joel Becker at Aug 25, 2011 at 9:02 pm
    I think reducing the number of steps like that sounds great. Your solution
    seems lots more intuitive to me. For now, i'm happy with at least not having
    to edit xml by hand :)
    Thanks for verifying my steps.
    -Joel
    On Thu, Aug 25, 2011 at 3:15 PM, Robert Zeigler wrote:

    Yup, that's about right. :)

    I never really thought about it being a lot of steps; I guess I always
    preferred having explicit control over how the join(s) work. But I see your
    point, and 90% of the time, a toMany uses a flattened relationship across a
    join table with no intermediate objects, so having this "default" behavior
    would be nice.

    Hibernate can auto-generate the join table. But it's always bugged me a
    little because it's been my experience that that it over-generate join
    tables (Eg: creating a join table for a one-to-many instead of associating
    the "to many" side with the fk of the "to one" side; you can do the latter,
    but it's way to easy to wind up with the former :).

    Still... what if we moved (or else duplicated?) the "toMany" checkbox in
    the join editor and had one next to each side of the relationship... and if
    both are selected, Cayenne would know you want a many-to-many and could
    potentially generate the join table and associated mapping information?
    ObjectEntity syncing also could be improved s.t. if both entities are
    around and you sync them, and there's no entity associated with the join
    table, and there are only pk columns in the join table, and there's no
    relationship already mapped between them, cayenne auto-generates the
    flattened relationship. The improvements above could potentially result in
    a workflow such as:

    * Create user table + attributes
    * Create exercise table + attributes
    * Create relationship in user table named "exercises" and map to "exercise"
    table; check "toMany"
    * Open up the relationship info dialog and put "users" into the reverse
    relationship and check "toMany"
    * Cayenne auto-generates the user_exercise table with pk exercise_id mapped
    to exercise.id (or whatever the Primary Key column is named in exercise)
    and pk user_id, and auto-adds the db relationship mapping, including
    adjusting the the target entity of the "exercises" relationship in the user
    table to be the join table.
    * Create the user object entity
    * Create the exercise object entity (toMany relationship to user added at
    this point because user entity exists)
    * sync user object entity with user table. toMany relationship to
    exercises added.

    The key thing with the entity syncing would be a set of rules along the
    lines of:
    Does Entity A have a toMany db relationship to another db entity (Entity
    B)?
    No? Stop
    Yes: Is entity B mapped as an object entity already?
    Yes? Stop
    No: Does entity B one and only one other attribute that is also a
    primary key?
    No? Stop
    Yes? Does entity B have a toOne db relationship that uses the 2nd
    primary key where the reverse relationship from entity C to entity B is
    toMany?
    No? Stop
    Yes: Generate a flattened relationship from A to C through the join
    table if it doesn't already exist.

    I definitely think this sort of improvement is worth having. Not sure
    whether the proposal outlined above is what we want. But I think it would
    cover the majority of cases.

    Robert

    On Aug 25, 2011, at 8/252:24 PM , Joel Becker wrote:

    I think I finally understand how I can create a many-to-many relationship
    between objects in the modeler, but there are a lot of steps and some seem a
    bit repetitive, so I want to ask if this is really the best / most
    convenient way of doing it. In my example, I have Users and Exercises: users
    perform a number of exercises, and exercises are performed by a number of
    users, so we have a many to many. I would like to be able to just go to the
    User object and add a many-to-many relation to Exercise, but as far as I can
    tell i actually have to perform these steps (please correct me if I'm wrong;
    i worked through these steps as I typed this so I know this at least works):
    * Create a join table USER_EXERCISE to join the User pk and Exercise pk
    * Create user_id and exercise_id attributes
    * In the USER_EXERCISE table, create a to-one relationship to the User
    pk, and a to-one relationship to the Exercise pk:
    * Select the USER_EXERCISE entity
    * Select the Relationships tab
    * Click the Create Relationship button
    * Name it "exerciseUsers"
    * Select the Target to be the USER table
    * Click the inspector button (first change focuse from Target
    dropdown so that the inspector button is enabled)
    * In the DbRelationship Info dialog:
    * Name the Relationship "userExercises" and the Reverse Relationship
    "exerciseUsers"
    * Add a join by clicking the Add button
    * For Source select exercise_id, and for Target select id (pk
    from EXERCISE table)
    * Repeate the same steps for the relation to the Exercise pk, naming the
    relationship "userExercises"
    * Create a flattened many-to-many relationship in the User object
    * Select the User object (not the table)
    * Go to the Relationships tab
    * Click Create Relationship
    * Click the Edit Relationship button. In the ObjRelationship Inspector
    dialog that opens:
    * Name the relationship "exercises"
    * for Target select Exercise
    * Under "Mapping to DbRelationships" section, select "exercises"
    relationship, then the "userExercises" relationship (from the join table)
    * Click "Select Path"
    * Click "Done"
    * Create a flattened many-to-many relationship in the Exercise object using
    the same steps
  • Durchholz, Joachim at Aug 26, 2011 at 11:42 am
    I think the problem with Modeler isn't this particular workflow, it's generally too complicated.

    Maybe it would be easier on users if they could edit relationships directly, instead of having to jump around between the tables on the two side of the relationship.

    Here's a simplified workflow for the standard case, just as an example.
    * Select a table.
    * Say "relationship with other table".
    * Have a four-column display. Columns 1 and 4 show the left and right table's fields, with PK fields first.
    * Let the user drag-and-drop columns from 1 to 2, and from 4 to 3.
    * Columns 2 and 3 specify the fields that go into the JOIN. The JOIN would be over equality between column values.
    * Columns 2 and 3 could be pre-filled with the PK fields of the two tables. This would more-or-less automatically Do The Right Thing in most cases.
    * Have checkboxes that make the relationship both ways, just left-to-right, or just right-to-left.
    * The one-to-many stuff should not be specified directly but inferred from uniqueness constraints. Those can be partly retrieved in the database schema, but views aren't usually reported, so the user must be able to specify additional uniqueness constraints. (A side is "to-many" if there is no uniqueness constraint with all of its fields in columns 2 resp. 3.)

    Potential complications:
    * Flattened relationships. Not sure whether a six-column display or a separate editing dialog for flattening would be easier to use.
    * Flattened relationships that use an "octopus" table (i.e. a join table with more than two to-one relationships).
    * JOINs that use computed expressions. Maybe columns 2 and 3 should be editable text fields, and dropping a field in it just edits it to contain that field name. (Uniqueness constraints would need to be expressed over computed expressions as well. If you do SQL parsing you can infer these, at least partially.)
    * Whether all this table-based relationship specification stuff should actually work from the Java class level, too. Some people prefer to specify on the Java level, some on the database level; in both approaches, specifying stuff on one level should automatically infer the equivalent stuff on the other.

    Note that while this looks like a full specification, I'm pretty sure it won't be doable that way. I don't know enough about what Cayenne supports to have a chance at writing a complete spec for anything.
    It's more an example demonstration of the abstraction level at which I as an application programmer am at home, in the hope of giving the Modeler developers an idea what kind of improvements would be most useful.

    Thanks for reading,
    Jo

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categoriescayenne
postedAug 25, '11 at 7:24p
activeAug 26, '11 at 11:42a
posts4
users3
websitecayenne.apache.org

People

Translate

site design / logo © 2022 Grokbase