How to construct a hibernate query that uses a single element of all elements in a collection?

Question

Here's the situation:

I have a datatype C that has a one-to-many reference to Datatype P. Datatype P has an optional R reference (one to one).

My attempted query was as follows (it was a count, but it will also be used to pull data)

FROM C WHERE ... AND P.R.rProperty LIKE 'BLAH%';

I get a

org.hibernate.QueryException: illegal attempt to dereference collection 
[C.C_SEQUENCE_NUMBER.P] with element property reference [R] [select count(*) FROM C a WHERE a.DATE_FIELD >= ? AND a.DATE_FIELD <= ? AND a.P.R.rProperty LIKE ?]

Any hints/suggestions?

Answer 1

Am I missing something here or should it be

FROM C c WHERE ... AND c.p.r LIKE 'BLAH%';

Hmm, let's rewrite this properly

Class Foo {
   public List<Bars>getBars();
}

class Bar {
   public Baz getBaz();
}

Where Foo is your C, Bar is your P and Baz is your R Then your hql would be like

from Foo f where f.bars.baz like 'BLAHH'

Answer 2

You could start from the entity P, provided this has a 'back-link' to the containing entity C, a so called bidirectional mapping.

You can achieve this with HQL by specifying a select or Criteria by using a ResultTransformer.

e.g.

select p.C from P p where p.r.property like 'BLAH%';

or

s.createCriteria(P.class)
 .createAlias("c","toSelect")
 .createCriteria("r")
 .add(Restrictions.like("property","BLAH%")
 .setResultTransformer(new ResultTransformer() {
      public Object transformTuple(Object[] tuple, String[] aliases) {
        for (int i = 0; i < aliases.length; i++) {
          if ("toSelect").equals(aliases[i])) {
            return tuple[i];
          }
        }
        throw new RuntimeException("What?");
      }
     ...
   })
 .list();

Answer 3

Try the following

"FROM EntityC c inner join EntityP p WHERE p.R.rProperty LIKE 'BLAH%'"