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
