Get latest records by date?

bschmitt · **Joined:** Mon Jan 30, 2006 4:33 pm **Posts:** 4

Hi all...
I am fairly new to NHibernate, and still learning so bear with me if this is easy...

I have setup all my classes and can retrieve records, I have a complex set of queries that a user can make to help filter down a set of records. To do this I have been using the ICriteria, which is working great. However, I now need to be able to filter and retrieve only the most recent item in the set.

For example if I have the following records I need to only get ID=2

Code:

ID    Date          Item     Value
1      12/21/04    XYZ      123
2      11/02/05    XYZ      321
3      12/03/04    XYZ      456

Thanks for any help...

Brian

pelton · **Posted:** Tue Jan 31, 2006 11:47 am

I'm new to Hibernate too, but one way you could do this is with a subselect:
But this requres using IQuery objects instead of ICritiera.

Here is a template for the query string:

Code:

 from {name of your class} as u
where u.Date = (select max(u1.Date)
                  from {name of your class} as u1)

To use this, you need to create an IQuery object:

Code:

IQuery = ISession.CreateQuery(qryString);

--Brian

pelton · **Posted:** Tue Jan 31, 2006 12:54 pm

pelton wrote:

But this requres using IQuery objects instead of ICritiera.

I'm thinking you would like to stick to ICriteria objects. So I thought I would give it a try, but I'm having no luck. I'm not even sure if it is possible, but if it is possible, my thought is that you will need to use NHibernate.Expression.SQLCriterion. There are slim findings on the message board on that topic.

I tried to create an SQLCriterion using my subselect in the prior post, but the constructor wants an array of values and an array of NHibernate.Type.IType's. I tried to just pass in nulls, but that didn't work.

xenonk · **Joined:** Tue Jan 31, 2006 1:07 pm **Posts:** 1

Hope this helps:

criteria.AddOrder(new Order("DateCreated", false));
criteria.SetMaxResults(1);
IList result = criteria.List();
if(result.Count > 0)
return result[0] as Customer;
else
return null;

bschmitt · **Joined:** Mon Jan 30, 2006 4:33 pm **Posts:** 4

Thanks for the input so far, I think I should have been more clear in my original post.

I have other records that I will need to retreive from the same table the XYZ may be one of hundreds. But I need the most current one of each.

So I may have:

Code:

ID    Date          Item     Value
1      12/21/04    XYZ      123
2      11/02/05    XYZ      321
3      12/03/04    XYZ      456
4      12/04/04    ABC      912
5      11/09/03    MNO      891

And I need to get ID's 2 and 4 and 5 back.

Right now I am looking into using Expression.Sql To achieve this... But not sure... I will let you know if I find anything, or if any of you happen to come up with an Idea....

Thanks

pelton · **Posted:** Tue Jan 31, 2006 2:59 pm

bschmitt wrote:

I have other records that I will need to retreive from the same table the XYZ may be one of hundreds. But I need the most current one of each.

Okay, again my solution is using IQuery objects. If this works for you, then use it:

Its still a subselect, but now you just need to get the max date for a given Item. So the query string looks like this:

Code:

 from {name of your class} as u 
where u.Date = (select max(u1.Date) 
                  from {name of your class} as u1
                 where u1.Item = u.Item)

Feel free to email me or post back...

--Brian

pelton · **Posted:** Tue Jan 31, 2006 5:11 pm

pelton wrote:

Okay, again my solution is using IQuery objects. If this works for you, then use it:

Okay, I am starting to fall in love with Query By Example (QBE) so I wanted to make this work too, since I use effective dated tables a lot in my business applications.

I have it working!!! But there is a downside. The SQLCriterion is passed on to the database, so it isn't as clean as using HQL that works on the Objects themsevles. This means that the SQL string needs to match your database tables, not your object properties. (If yours happen to match, then no big deal, but my database tables are a little different so I ran into exceptions) Also be sure to use the name of the actual database table rather than the name of the object's class.

Okay here is my solution for your example:

Code:

ICriteria c = Session.CreateCriteria(SearchObject.GetType());
string SQL = "{alias}.date = (select max(effdt.date from {YOUR TABLE NAME} effdt where effdt.Item = {alias}.Item)";

NHibernate.SqlCommand.SqlString sqlObject = new NHibernate.SqlCommand.SqlString(SQL);

object[] emptyObject = new object[] { };
NHibernate.Type.IType[] emptyIType = new NHibernate.Type.IType[] { };

SQLCriterion e = new SQLCriterion(sqlObject, emptyObject, emptyIType);

c.Add(e);

c.List();

Lookout: I had to re-type the code into this posting, so there could be typos!

--Brian

bschmitt · **Joined:** Mon Jan 30, 2006 4:33 pm **Posts:** 4

Brian,

Thanks for your help... I hope you were able to get some work done yesterday.

For the record, I used a combination of your code and the ICriteria:

Code:

Criteria.Add(Expression.Sql("{alias}.data_date = (SELECT MAX(data_date) FROM Market_Data mddte WHERE mddte.market_survey_id = {alias}.market_survey_id)"))

I had to do it this way to fit in with the Criteria that I was using up to this point.

Was this an acceptable approach?
Does anyone see a problem with it?

pelton · **Posted:** Wed Feb 01, 2006 12:07 pm

bschmitt wrote:

Thanks for your help... I hope you were able to get some work done yesterday.

Glad it worked. Actually this helped me out too. I didn't think it could be done using Critiera, so now I have more options when working on my project.

This is my first time working with NHibernate, so I'm not sure if this is a good approach, but I don't see anything evil about it. If it works, and it makes sense, that's pretty good.

--Brian

pelton · **Posted:** Wed Feb 01, 2006 12:13 pm

bschmitt wrote:

Code:

Criteria.Add(Expression.Sql("{alias}.data_date = (SELECT MAX(data_date) FROM Market_Data mddte WHERE mddte.market_survey_id = {alias}.market_survey_id)"))

Now that I re-read your code, I see you used "Expression.Sql". Where's that??? I don't see that in my NHibernate expression namespace. I have version 1.0.2.0. Which version are you using?

Yours is much cleaner, you didn't have to pass in empty arrays like I did.

bschmitt · **Joined:** Mon Jan 30, 2006 4:33 pm **Posts:** 4

I'm also using 1.0.2
It's in the NHibernate.Expression.Expression Namespace

Quote:

Public Shared Function Sql(ByVal sql As String) As NHibernate.Expression.ICriterion
Member of: NHibernate.Expression.Expression
Summary:
Apply a constraint expressed in SQL

Parameters:
sql:

Return Values:

Here is the Relevant Code: (in VB)

Code:

Protected m_Session As ISession

m_Session = NHibernateHttpModule.CurrentSession
Dim Criteria As ICriteria = m_Session.CreateCriteria(GetType(Market_Data))

Criteria.Add(Expression.Eq("Company", CompanyID))
... {Other Code} ...
Criteria.Add(Expression.Sql("{alias}.data_date = (SELECT MAX(data_date) FROM Market_Data mddte where mddte.market_survey_id = {alias}.market_survey_id)"))

pelton · **Posted:** Wed Feb 01, 2006 12:38 pm

bschmitt wrote:

I'm also using 1.0.2
It's in the NHibernate.Expression.Expression Namespace

Ahh yes I see it now. Thanks!

--Brian