[Cayenne-user] Best way to create many-to-many relationship in Cayenne Modeller

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

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

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