Hi,

I've been using a many to many to represent the following relationship.

class Leg {
}

class Strategy {
List<Leg> legs
}

I map the legs inside of Strategy with a @ManyToMany and an @IndexColumn (ordering will matter). But now I'm running into some conceptual issues that I haven't been able to figure out.

First, I was hoping to define a strategy by the legs which it posses. That is, the legs that compose a strategy define it's uniqueness such that if StrategyA is composed of Leg 1, 2, and 3 then StrategyB may not be composed of Leg 1, 2, and 3. However I don't see a way to do this with Hibernate or with SQL (there is a table that joins Strategy and Legs but nowhere do I see a way to relate all the legs of a certain strategy in a way that they can't repeat). Does this sound possible or do I need a separate key to define my uniqueness (perhaps a string (@NaturalId) that composes the unique keys of the legs)?

Secondly, I can't figure out how to query against the Strategy's legs. Whether or not I can get uniqueness by legs I still want to query by what legs are in my list. I normally use Criteria for everything (realizing this might be a bad idea... but I could always optimize the choice away when I have to) and attempted the following:

DetachedCriteria.forClass(Strategy.class).add(Restrictions.eq("legs", legs));

where legs is a List<leg> but I keep getting all sorts of errors that make me think I'm doing this all wrong. When I looked online for examples the closes I could find was:

public List<DriversLicence> findDriversLicencesWith(List<LicenceClass> licenceClasses) {
String hqlString = "select dl from DriversLicenceImpl dl where 1=1 ";
for (int i = 0; i < licenceClasses.size(); i++) {
hqlString += " and :licenceClass" + i + " = some elements(dl.licenceClasses)";
}

Query query = getSession().createQuery(hqlString);
for (int i = 0; i < licenceClasses.size(); i++) {
query.setParameter("licenceClass" + i, licenceClasses.get(i));
}
return query.list();
}

Here we see an HQL being built by loops and specifically adding "where" for each item in the list. If I have to do it this way I don't mind but I want to make sure I'm not missing out on some more elegant solution. Also I found a solution using criteria but I can't even make sense of it...

for (LicenceClass licenceClass : licenceClasses) {
criteria.add(Restrictions.sqlRestriction("? = some(select " + LicenceClass.PRIMARY_KEY + " from " +
LICENCE_CLASS_JOIN_TABLE + " where {alias}." +
DriversLicence.PRIMARY_KEY + " = " + DriversLicence.PRIMARY_KEY + ")",
licenceClass.getId(), Hibernate.LONG));
}

This is doing the same select as the loop above... looks cleaner but I don't understand waht it is doing completely, I see that it is selecting a LicensceClass but I don't understand what {alias} means or why DeriversLicense.PRIMARY_KEY is being used so much...

Any help is greatly appreciated. Thanks.