I've implemented a many-to-many relationship for the first time, and there are a couple of things which are unclear to me. I have two classes, Team and Competition. A Team can belong to many Competitions, a Competition can contain many Teams. So, I have successfully mapped this as a many-to-many using a bridging table, team_competition.

My first problem is now how to query to get, say, all the Teams in a given Competition. With a one-to-many (which I'm used to), I'd do:



But this, of course, no longer works with a many-to-many, as Team has a competitions Collection property, not a single Competition property.

The second problem is how to specify an order-by for the returned collection. Once again, with a one-to-many I can specify an order-by column in the many table. But what do I do when there's a bridging table in the middle?

TIA

[Hibernate 2.1.8]

you can use something like the snipet below to map a collection on your Team class



where the table's fields are:
Team: { team_id, ..... }
Competition: { competition_id, ..... }
Competition_Team: { competition_id, team_id, order_field, ... }

I hope this helps

Emilio

So how do I populate the Competition_Team.order_field column? At the moment the handling of the bridging table is done automatically for me by Hibernate.

Hmm, I said I had many-to-many working, largely. In fact, I don't, and I can't see why. Here's the relevant chunk from Team.hbm.xml:

<set
name="competitions"
table="team_competition"
lazy="false"
inverse="false"
cascade="save-update"
sort="unsorted">

<key
column="team_id"></key>

<many-to-many
class="myproject.om.Competition"
column="competition_id"
outer-join="auto" />

</set>

Here's the matching chunk from Competition.hbm.xml:

<set
name="teams"
table="team_competition"
lazy="false"
inverse="false"
cascade="save-update"
sort="unsorted">

<key
column="competition_id"></key>

<many-to-many
class="myproject.om.Team"
column="team_id"
outer-join="auto" />

</set>

I could swear this is just as in the examples I've seen. And yet it doesn't work when I run the following code, which retrieves an existing newly created Competition object from the database and then attempts to create and save 4 Team objects with this Competition assigned to them.




When I look at the database after this process has run, the bridging table ('team_competition') only has one record in it, which is for the last of the teams created in the code above. I experimented with setting 'inverse=true' on one of the tables, but this ended up with no bridging records at all being created. I must be doing something fundamentally wrong, but I really can't see what it is. Help!

This is getting increasingly bizarre. As an experiment, I tried the following, where instead of adding the Competition to the Team, I add the team to the Competition (both classes have a Set of the other and an addXXX method which adds the respective object to the set).



What happens now is that bridging records are created for the second and fourth teams, but not the first or third. This makes no sense at all to me.

Is there any good documentation of many-to-many relations which people know about other than in the Hibernate reference (where the coverage is rather meagre)?

Well, the answer seems to be that you have to specify 'lazy="true"' for the relationships. That sorted it out.

Is this kind of stuff actually documented anywhere?