I have an object schema that gets determined at runtime; we allow users to add custom fields to the Item object. We have:
Item
Item.id
Item.values (one-to-many FieldValue objects)
FieldValue
FieldValue.id
FieldValue.value
FieldValue.field (many-to-one Field objects)
Field
Field.id
Field.name
There's a bunch of other fields, but this sums up the relevant ones. Because this database can hold a sizeable number of Items, I'm passing in the following HQL query to the Hibernate Query object and paging the results as below:
The query (get Items, filtered by one Field/FieldValue):
Code:
SELECT i FROM Item i
WHERE i.values.id in (Select fv.id From FieldValue fv where fv.field.id=? AND UPPER(fv.value) LIKE ?)
The code:
Code:
private List findByQueryParamArrayPaginated(String query, Object[] params, int page, int pagesize) throws DataAccessException {
Session s = this.getSessionFactory().openSession();
Transaction tx = s.beginTransaction();
s.setFlushMode(FlushMode.COMMIT);
Query q = s.createQuery(query);
q.setMaxResults(pagesize);
q.setFirstResult(page * pagesize);
for (int i = 0; i < params.length; i++)
q.setParameter(i, params[i]);
List l = q.list();
tx.commit();
s.flush();
s.close();
return l;
}
This works. What I can't figure out is how to extend this to sort by a given Field/FieldValue within the DB.
Order by doesn't seem to allow a nested query, and I'm up against a wall trying to figure out clever ways to build the sort clause I want elsewhere in the query & mapp that to a variable I put in order by. If this were allowed, the equivalent of what I want would look something like this:
Code:
SELECT i FROM Item i
WHERE i.values.id in (Select fv.id From FieldValue fv where fv.field.id=? AND UPPER(fv.value) LIKE ?)
ORDER BY (Select fv2.id From FieldValue fv2 where fv2.field.id=? AND UPPER(fv2.value) = ?)
(Hibernate 3.1.1 w/ Postgres 8.2)
Any ideas? Greatly appreciated...
-Greg
Login
FAQ
Search