find obj based on multiple many-to-many association criteria

ymidt · **Joined:** Thu Jan 08, 2009 11:04 am **Posts:** 1

Lets say I have a Book object with a many-to-many association of Authors.

I want to find all Books that have *both* Author A *and* Author B in their Set<Author>.

I tried a criteria search using Restrictions.conjunction, but it results in SQL that tests each Author for both =A and =B, resulting in no matches.

Code:

   public List<Book> findByAuthors(Collection<Author> authors) {
      DetachedCriteria criteria = DetachedCriteria.forClass(persistentClass);
       DetachedCriteria authCriteria = criteria.createCriteria("authors");
       authCriteria.add(Restrictions.conjunction());
      for (Author author : authors) {
          authCriteria.add(Restrictions.eq("name", author.getName()));

      }
      return findByCriteria(criteria);
   }

Is there a way to code this so I will get Books with at least these two Authors, A and B?

Advice appreciated!

-- David

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Join BOOKS twice with the Author's table, under different alias. Add one where condition to each aliased table

mmerder · **Posted:** Fri Jan 09, 2009 4:27 am

Quote:

Join BOOKS twice with the Author's table, under different alias. Add one where condition to each aliased table

I think this will cause an exception like "duplicate association path". I think Criteria is not going to help you, but plain SQL. There you could join authors with authors on same bookids, filter for the authors you need and then get the books.

pkleindl · **Joined:** Thu Jan 08, 2009 3:48 pm **Posts:** 168

I think that's why gonzao_diaz wrote "under different alias", this should solve this issue

In SQL i would try something like
WHERE author_name in (A,B)
GROUP BY book
having count(author)=2

this only needs one join

But not sure how this is doable in Criteria

mmerder · **Posted:** Fri Jan 09, 2009 4:45 am

Quote:

I think that's why gonzao_diaz wrote "under different alias", this should solve this issue

Thats what I tested:

Code:

session.createCriteria(Book.class).createAlias("authors", "a1").createAlias("authors", "a2")...

will cause

Quote:

duplicate association path: authors