Two tables that this problem relates to are BUSINESS and BUSINESS_KEYWORD. The keyword table contains a BUSINESS_ID and a KEYWORD (string value). The idea is that we can associate keywords to businesses and search for a business by that keyword (where keyword like 'food%' say). I have it mapped currently as a bag:



The sorting is for administrative use only (easier to find one out of a 100 if they're sorted). Anyway, I basically want to do an SQL query like:



I can't find anything in the Hibernate doco's about how to do a query like this though. All the examples are indexed bags. Is there any way to do something like:



Thanks!
James

session.filter() see api docs.

here is how I do something similar ... this.status.name is filtering on many-to-one attribute

Object[] values = new Object[3];
values[0] = "published";
values[1] = new java.util.Date();
values[2] = new java.util.Date();
net.sf.hibernate.type.Type[] types = new net.sf.hibernate.type.Type[3];
types[0] = Hibernate.STRING;
types[1] = Hibernate.DATE;
types[2] = Hibernate.DATE;
java.util.List playlists = (java.util.List)sess.filter(frame.getList(),
"where this.status.name = ? and (this.showEdate is null or this.showEdate > ?) and (this.showSdate is null or this.showSdate <= ?) order by sort_order",
values,
types);

But this only works in Hibernate 2.1, because it is a collection of values, not an association.

worked like a charm. you rule gavin!