OR with multiple Criteria trouble

mauriciog · **Joined:** Thu Mar 06, 2008 10:38 am **Posts:** 1

Hi, I have the following class

Code:

class File {
  List<Document> documents;
  List<Invoice> invoices;
}

properly mapped, actually Its far more complex but I want to keep it simple to explain my problem.
Document and Invoice classes has a "notes" property. I want to search all the files where the notes of documents OR the notes of invoices are equals to some value. The whole is a more complex query and I cant use HQL.

Code:

Criteria criteria = session.createCriteria("File");
Criteria criteriaDocument = criteria.createCriteria("Document");
Criteria criteriaInvoice = criteria.createCriteria("Invoice");

   criteriaDocument.add(Restriction.ilike("notes","somevalue"));
   criteriaInvoice.add(Restriction.ilike("notes","somevalue"));

I cant use Restriction.or() because its criteria aware. The SQL generated is "document.notes = 'something' AND invoice.notes='something'" but I cant find a way to do some OR between those criterias.

Yoann56 · **Joined:** Thu Nov 15, 2007 11:27 am **Posts:** 34

I haven't find gegeric way to do that with criteria, so if you can, go with hql, or do it with 2 request and merge the results. (or wait a suitable response :) )

kuzhali · **Posted:** Thu Mar 13, 2008 1:38 am

Hi,

Did you try with disjunction ?
It is better to use disjunction for the or conditions.
I reformed your criteria as below.

Criteria criteria = session.createCriteria("File");
criteria.createAlias("Document","Document");
criteria.createAlias("Invoice","Invoice");
criteria.add(Restriction.disjunction().add(Restriction.ilike("Document.notes","somevalue"))
.add(Restriction.ilike("Invoice.notes","somevalue")));