Querying Many-To-Many relationship with Hibernate Criteria API

Question

I've the following many to many relation: File 1 --- * File_Insurer * --- 1 Insurer. I'm trying to query this relation using the Criteria API (Active Record) to get Files that meet ALL specified Insurers (Get all Files where Insurer.Id == 2 AND Insurer.Id == 3).

Mapping files (parts):

File

[HasAndBelongsToMany(typeof(Insurer), Table = "Insurer_File", ColumnKey = "IdFile", ColumnRef = "IdInsurer")] public virtual IList Insurers { get { return insurers; } set { insurers = value; } }

Insurer

[HasAndBelongsToMany(typeof(File), Table = "Insurer_File", ColumnKey = "IdInsurer", ColumnRef = "IdFile")] public virtual IList Files { get { return files; } set { files = value; } }

I've tried many options:

DetachedCriteria dc = DetachedCriteria.For(); dc.SetResultTransformer(new DistinctRootEntityResultTransformer()); dc.CreateCriteria("Insurers").Add(Expression.Eq("Id", long.Parse("2"))); dc.CreateCriteria("Insurers").Add(Expression.Eq("Id", long.Parse("3"))); List searchResults = File.FindAll(dc).ToList();

That gives me a error "duplicate association path: Insurers".

Next option:

DetachedCriteria dc = DetachedCriteria.For(); dc.SetResultTransformer(new DistinctRootEntityResultTransformer()); dc.CreateCriteria("Insurers").Add(Expression.And(Expression.Eq("Id", long.Parse("3")), Expression.Eq("Id", long.Parse("2")))); List searchResults = File.FindAll(dc).ToList();

The result list is empty (but shouldn't).

Next option with alias:

DetachedCriteria dc = DetachedCriteria.For(); dc.SetResultTransformer(new DistinctRootEntityResultTransformer()); dc.CreateAlias("Insurers", "i").Add(Expression.Eq("i.Id", long.Parse("2"))).Add(Expression.Eq("i.Id", long.Parse("3"))); List searchResults = File.FindAll(dc).ToList();

The result list is empty again - strange.

Next try:

DetachedCriteria dc = DetachedCriteria.For(); dc.SetResultTransformer(new DistinctRootEntityResultTransformer()); List insurerIds = new List(); insurerIds.Add(2); insurerIds.Add(3); dc.CreateCriteria("Insurers").Add(Expression.In("Id", insurerIds)); List searchResults = File.FindAll(dc).ToList();

This works somehow, but the result set contains a all possible options (OR) - it's not an exact match.

Answer 1

Cross-posted in the Castle forums (with solution).

Answer 2

This has been answered already - see my take on it below. The hibernate site seems to be down, but check out this copy of chapter 11, HQL, of the Hibernate user guide.

public List<Files> findFilesForInsurers(Insurer... insurers) {
    StringBuilder hql = new StringBuilder();
    hql.append("select f from Files ff where 1=1 ");
    for (int i = 0; i < insurers.length; i++) {
        hql.append(String.format(" and :i%d in elements(ff.insurers)", i));
    }
    Query query = getSession().createQuery(hql.toString());
    for (int i = 0; i < insurers.length; i++) {
        query.setParameter("i" + i, insurers[i]);
    }
    return query.list();
}

Giving HQL like this:

select f from Files ff where 1=1
    and :i1 in elements(ff.insurers)
    and :i2 in elements(ff.insurers)

I suspect the SQL you are trying to achieve is something like this, although i couldn't tell you exactly what the above HQL translates into...

select f.* from files f
    left outer join files_insurers fi1 on fi1.files_id = f.id
    left outer join files_insurers fi2 on fi2.files_id = f.id
where 1 = 1
    and fi1.insurers_id = :i1
    and fi2.insurers_id = :i2;