Help translating a self-referential query into HQL or Criter

david.berkman · **Joined:** Tue Mar 03, 2009 8:42 pm **Posts:** 2

I need help with a query, translating it to either HQL or Criteria.

I have a table containing a long id, a long reference to another object, a timestamp, and a float representing a dollar value. Nothing fancy, so the resultant table looks like...

| id | other_id | timestamp | dollars |

And the class mapped to this table contains the fields...

private long id;
private Other other;
private Date timestamp;
private float dollars;

...where the Other class is similarly simple and mapped as a one to one relationship.

The table rows represent, for any instance of Other, a series of dates and a dollar value assigned to those dates. Call the class PaymentRule.

Sorry for being a bit vague, but I'm trying to avoid disclosing anything which might be proprietary, and I believe the background to the problem is easy enough to describe this way without resorting to class files and hbm files, although I'm willing to provide examples of both.

My basic problem is that I'd like to select, for each instance of Other, that dollar value associated with the date closest to, but not greater than, some cut-off date. If no such date exists for a requested instance of Other, then no return is necessary.

So, given the rows...

| id | other_id | timestamp | dollars |
| 1 | 4 | '2009-02-15 00:00:00' | 15.00 |
| 2 | 4 | '2009-02-11 00:00:00' | 11.00 |
| 3 | 4 | '2009-02-18 00:00:00' | 18.00 |

...looking for the Other instance with id '4' on or before the cut-off date of '2009-02-22 00:00:00', I'd expect a return PaymentRule with id of '3', the row representing the object which refers to the correct instance of Other, and has the date closest to but no greater than the cut-off. If the cut-off date was '2009-02-16 00:00:00', then the return PaymentRule should be the object with instance id '1'.

The sql for this query would look something like...

SELECT pr.id, x.other_id
FROM paymentrule pr
INNER JOIN (
SELECT other_id other, max(timestamp) maxtime
FROM paymentrule
WHERE timestamp <= ? and other_id = ?
GROUP BY other_id
) as x where pr.other_id = x.other AND pr.timestamp = x.maxtime

How do I do this in HQL or Criteria?

Any help appreciated,

David

nordborg · **Posted:** Wed Mar 04, 2009 4:01 am

What about?

Code:

String hql = "select pr from PaymentRule pr where pr.other = :other and pr.timestamp <= :cutoff order by pr.timestamp desc";
Query q = session.createQuery(hql);
q.setTimestamp("cutoff", aTimestamp);
q.setInteger("other", otherId);
q.setMaxResults(1);
List result = q.list(); // Should contain 0 or 1 entry

mmerder · **Posted:** Wed Mar 04, 2009 4:22 am

And here a query without maxResult:

Code:

select pr from PaymentRule pr where pr.other.id = :other and pr.timestamp = (select max(pr2.timestamp) from PaymentRule pr2 where pr2.timestamp <= :cutoff)

Of course it will only have one result if (timestamp, other) is unique.

david.berkman · **Joined:** Tue Mar 03, 2009 8:42 pm **Posts:** 2

For any instance of Other, there may be several entries. Grouped by each instance of Other there's 1 max timestamp entry. I'm looking for a query that, given a set of Other(s), where...

pr.other in (:others)

...the query returns 1 PaymentRule for each instance of Other, where that PaymentRule has the max timestamp out of those timestamps which contain the same Other reference.

Does that make more sense?

Thanks,
David