Critera with nested properties

rwallace · **Joined:** Mon Jun 06, 2005 12:10 pm **Posts:** 20

I'm trying to create a quick search type of interface for the application I'm working on. What I'm trying to do is use the Criteria API to construct the search parameters. One of the searchs requires that the where be done on a nested property of the object. So, rather than searching on something like Company.name, the search should be done on Company.industry.name. I've tried it with the following:

Code:

DetachedCriteria detachedCriteria = DetachedCriteria.forClass (Company.class);
detachedCriteria.add (Restrictions.eq ("industry.name", "%consulting%"));
Criteria criteria = detachedCriteria.getExecutableCriteria (session);

But this just gives me the result

Code:

Caused by: org.hibernate.QueryException: could not resolve property: industry.name of: Company

Is there some other way I can get this working or should I forgoe the Criteria API and just try and build an HQL string (which I really don't want to have to do because that's always error-prone in my experience)?

Thanks

abg1979 · **Joined:** Fri Sep 09, 2005 11:35 am **Posts:** 101

Use

Code:

DetachedCriteria detachedCriteria = DetachedCriteria.forClass (Company.class).createCriteria("industry");
detachedCriteria.add (Restrictions.eq ("name", "%consulting%"));

kgignatyev · **Posted:** Fri Oct 07, 2005 3:51 pm

In adition to previous poster:
Since I see % symbols in the value I suspect that you need to use Resrioction.like or Restiction.ilike

rwallace · **Joined:** Mon Jun 06, 2005 12:10 pm **Posts:** 20

abg1979

Thanks, I wasn't sure if that would do what I thought it would.

kgignatyev
The % was just a copy & paste error, I didn't mean to leave them in there.

Thanks again guys.

rwallace · **Joined:** Mon Jun 06, 2005 12:10 pm **Posts:** 20

One more question on this subject.

I see that to do this you need to use

Code:

DetachedCriteria detachedCriteria = DetachedCriteria.forClass (Company.class).createCriteria("industry"); 
detachedCriteria.add (Restrictions.eq ("name", "Consulting"));

Does that limit being able to use criteria on other nested properties? I mean, if I have another property like address on Company and want to find all companies in the state of CA in the Consulting industry how can I do that? Or can I do that?

abg1979 · **Joined:** Fri Sep 09, 2005 11:35 am **Posts:** 101

rwallace wrote:

One more question on this subject.

I see that to do this you need to use

Code:

DetachedCriteria detachedCriteria = DetachedCriteria.forClass (Company.class).createCriteria("industry"); 
detachedCriteria.add (Restrictions.eq ("name", "Consulting"));

Does that limit being able to use criteria on other nested properties? I mean, if I have another property like address on Company and want to find all companies in the state of CA in the Consulting industry how can I do that? Or can I do that?

You can do it by keeping reference to the criteria objects.
Like here

Code:

DetachedCriteria detachedCriteria1 = DetachedCriteria.forClass (Company.class);
DetachedCriteria detachedCriteria2 = detachedCriteria1.createCriteria("industry");
detachedCriteria2.add(Restrictions.eq ("name", "Consulting")); 
DetachedCriteria detachedCriteria3 = detachedCriteria1.createCriteria("address");
detachedCriteria3.add(Restrictions.eq ("state", "CA")); 

rwallace · **Joined:** Mon Jun 06, 2005 12:10 pm **Posts:** 20

Thanks abg1979,

I figured that out just before the weekend so didn't get a chance to post back. I appreciate the reply. Now I'm running into a problem with the sorting on these sub-criteria. I have something like the following:

Code:

DetachedCriteria criteria = DetachedCriteria.forClass (Company.class);
criteria.add (Restrictions.ilike ("name", m_searchValue));
DetachedCriteria addressCriteria = criteria.createCriteria ("address");
addressCriteria.addOrder (isAscending () ? Order.asc ("city") : Order.desc ("city"));

When I do the criteria.getExecutableCriteria(session).list() Hibernate throws an exception stating the following:

Code:

Caused by: org.hibernate.QueryException: could not resolve property: city of: Company

The "address" property of Company is a mapped class of type Address with a subproperty of city. But it looks like rather than trying to apply the ordering on the address property it's trying to find a city property in the Company class. Since that doesn't exist it throws the exception.

Any ideas? Is this a bug with DetachedCriteria?

abg1979 · **Joined:** Fri Sep 09, 2005 11:35 am **Posts:** 101

rwallace wrote:

Thanks abg1979,

I figured that out just before the weekend so didn't get a chance to post back. I appreciate the reply. Now I'm running into a problem with the sorting on these sub-criteria. I have something like the following:

Code:

DetachedCriteria criteria = DetachedCriteria.forClass (Company.class);
criteria.add (Restrictions.ilike ("name", m_searchValue));
DetachedCriteria addressCriteria = criteria.createCriteria ("address");
addressCriteria.addOrder (isAscending () ? Order.asc ("city") : Order.desc ("city"));

When I do the criteria.getExecutableCriteria(session).list() Hibernate throws an exception stating the following:

Code:

Caused by: org.hibernate.QueryException: could not resolve property: city of: Company

The "address" property of Company is a mapped class of type Address with a subproperty of city. But it looks like rather than trying to apply the ordering on the address property it's trying to find a city property in the Company class. Since that doesn't exist it throws the exception.

Any ideas? Is this a bug with DetachedCriteria?

Try using aliases.

Code:

DetachedCriteria criteria = DetachedCriteria.forClass (Company.class);
criteria.add (Restrictions.ilike ("name", m_searchValue));
DetachedCriteria addressCriteria = criteria.createCriteria ("address", "address");
addressCriteria.addOrder (isAscending () ? Order.asc ("address.city") : Order.desc ("address.city"));

rwallace · **Joined:** Mon Jun 06, 2005 12:10 pm **Posts:** 20

I tried using the alias and that didn't work either. I wonder if it's because I'm not adding any criterion to the sub-criteria so it's just being ignored or something?

rwallace · **Joined:** Mon Jun 06, 2005 12:10 pm **Posts:** 20

Here's another update. I tried doing:

Code:

Criteria criteria = DetachedCriteria.forClass (Company.class);
DetachedCriteria addressCriteria = criteria.createCriteria ("address");
addressCriteria.add (Restrictions.ilike ("city", "%"));
addressCriteria.addOrder (isAscending () ? Order.asc ("city") : Order.desc ("city"));
System.out.println (criteria);

and that outputs:

Code:

DetachableCriteria(CriteriaImpl(Company:this[Subcriteria(address:)][name ilike %Ari%, city ilike %]))

So it looks like operations on the criteria object retured from the criteria.createCriteria() call are being performed on the main criteria class instead of the sub-criteria. Any ideas why this might be? Should I file a bug report?

Thanks,
Rich[/code]

abg1979 · **Joined:** Fri Sep 09, 2005 11:35 am **Posts:** 101

can you try with criteria instead of detachedcriteria ?
may help in finding the problem