Hibernate Community • View topic - How to write "group by" query in nHibernate.

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » NHibernate Users

All times are UTC - 5 hours [ DST ]

How to write "group by" query in nHibernate.

Page 1 of 1

[ 3 posts ]

Previous topic | Next topic

Author

Message

salu_ahmad

Post subject: How to write "group by" query in nHibernate.

Posted: Tue Apr 24, 2007 1:48 am

Newbie

Joined: Mon Nov 20, 2006 3:58 am
Posts: 12

I searched a lot on google regarding this issue and also on this forum.. also went through the documentation in which there r couple of examples... but i m unable to make my query run in nHibernate although it work fine in SQL query analyzer.

Code:

                string query = "SELECT MAX(bid.bidPrice) AS bidPrice" +
                                " FROM tblAuctionBidBO bid" +
                                " WHERE (bid.item_itemId IN (" + itemIdStr + "))" +
                                " GROUP BY bid.item_itemId";

                IQuery sqlQuery = session.CreateSQLQuery(query, "AuctionBids", typeof(AuctionBidBO));               
                
                return sqlQuery.List();

I read somewhere while searching that nHibernate doesn't have "group by" currently.. Is it right or was it for some specific case..?

Thanks a lot.

Top

tindbaek

Post subject: Same issue

Posted: Mon Jun 11, 2007 9:48 am

Newbie

Joined: Sat Mar 10, 2007 12:46 pm
Posts: 5

We have exactly the same problem as you.
We have a list of IDs, and we want to get a max value for each.

We are trying to write a query using the Criteria objects (not HQL or SQL), with no luck.

Did you ever solve the problem you posted here?

Top

sunraider

Post subject:

Posted: Mon Jun 11, 2007 12:51 pm

Newbie

Joined: Mon Jun 11, 2007 6:08 am
Posts: 3

Something like this should work(NOT TESTED), Hope this might help:

Code:

IList bids= sess.CreateCriteria(typeof(TblAuctionBidBO))
.Add ( Projections.Max( "BidPrice" ) )
.Add ( Expression.In( "ItemId" , itemIdArray ) )
.Add ( Projections.GroupProperty( "ItemId" ) );

The itemIdArray is the object[] passed to the expression.

Top

Page 1 of 1

[ 3 posts ]

Board index » Hibernate & Java Persistence » NHibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum