views:

113

answers:

1

Why Nibernate HQL can not handle the following query:

from Deal D where (D.ApprovalDate + INTERVAL 1 Year) < current_timestamp() <  (D.RenewalDate + INTERVAL -1 Year) 

knowing that INTERVAL and YEAR are keywords in MySQL, so this is kind of mixing Sql within Hql (unless Hql can handle date functions like so and I don't know) . The dialect is MySQLDialect

Its perfectly valid to execute this query

  SELECT '2005-01-01' + INTERVAL 1 Year;
+1  A: 

You can pass the Sql directly in the query using Criteria Query. Something like this

Session.CreateCriteria<Deal>()
.Add(Restrictions.Sql(D.ApprovalDate + " INTERVAL 1 Year < current_timestamp() < " + D.RenewalDate + " INTERVAL -1 Year")

The Restrictions.Sql will pass whatever you give it directly to the database as sql.

Adam
Thanks for this proposal, but I'm building HQL query dynamically. I will look into this solution, but do you think there is something powerful that works with hql, any ideas ?
jalchr
I'm curious your reason to choose HQL over the Criteria. Criteria Queries compile down to HQL so it may be possible to do what you are trying but I have never figured out how. I had similar issues needing to pass Sql directly and ended up using Criteria. Are you forced to use HQL?
Adam
Well, no! But If that melts down to hql, I wonder how that hql looks like :) ... Thanks again for your tremendous efforts
jalchr
Np, I'm digging through the NHibernate source code now to see if it is possible using HQL. I'll update you if I find a way. Hopefully the Criteria way can help you until then.
Adam
It did help. I hope you get good results with hql ... good luck
jalchr