Hello everyone, I've been trying to build a Criteria where one of the Restrictions' values depend on the data in one of the columns. I've been searching around and I haven't had much progress. Hopefully someone can put me in the right direction.

I'm building some kind of report module, and one of the functions is to generate the list of paid members for a particular day. The definition of a paid member is where his payment record (dateCreated) is less than the date parameter, and less than the (dateCreated + policyTerm). It's at the policyTerm that I'm stuck with - policyTerm is a value from another column.

I've been playing around with the query, and this SQL query works (MySQL):

SELECT * FROM payments
INNER JOIN policies
ON payments.policy=policies.id
WHERE payments.dateCreated < "2009-06-06 11:31:06"
AND "2009-06-06 11:31:06" < date_add(payments.dateCreated, INTERVAL policies.term MONTH);

The 2009-06-06 date is the date parameter that I need to pass to the function. My main problem is the interval - I have no idea how to incorporate that into the query.

Can anyone help in translating this to Criteria-based syntax? Or at least point me in the right direction. Thanks a lot!

What about:

select payments.*, date_add(payments.dateCreated, interval policies.term month) as c
from payments inner join policies
on payments.policy = policy.id
where payments.dateCreated < :thedatehere
and c > :thedatehere

This way, you only need :thedate. Hibernate has a Formula API that helps you do the formula in the select part.

It requires filtering the constructed "c" column from the output, though.

Thanks fge.

I was wondering if there was any Criteria-based solution, instead of doing HQL? Thanks for giving me the insight of aliasing the computation though, I might find use for that in the future :)

There most certainly is, but I'm just beginning with this API...



You're welcome. However, I wonder what this "interval xxx month" does?

It's a MySQL function that performs date mathematics. It performs something like Calendar.add(Calendar.MONTH, term) in java.

Thanks again, but I'm still looking for a criteria-based solution :( I'm currently looking at Restrictions.sqlRestrictions as a desperate move though.