I wrote the syntax off the top of my head, so there will be errors in the query. the essence of it is there.
"Lastly, I don't want to create another dto. Would that be ok if I add a 'average' property to my BuzzGallery class, and instead of returning a list of MyDto, I return a list of BuzzGallery entities ?" Can you... maybe, but this seems like a bad design to me. There is a fundamental difference between aggregated data an entities. To combine the two seems like it would cause more problems down the road. if you need this average all/most of the time you can map a property/ filed of BuzzGallery to a formula. the formula can be defined a as a subquery to calculate the average. this formula would be executed every time you fetch the entity from the database. why do you want to combine the two? On Jan 22, 9:20 am, graphicsxp <[email protected]> wrote: > Hi, > > Thanks for helping. But I'm having issues with your code (Criteria > part). At compiling : > > Error 1 'NHibernate.Criterion.Projections' does not contain a > definition for 'List' > > Error 2 The best overloaded method match for > 'NHibernate.Criterion.Order.Desc(string)' has some invalid arguments > > Error 3 'NHibernate.Transform.Transformers' does not contain a > definition for 'EntityToBeanCtor' > > I use NH 2.0, could that be the reason ? > > Lastly, I don't want to create another dto. Would that be ok if I add > a 'average' property to my BuzzGallery class, and instead of returning > a list of MyDto, I return a list of BuzzGallery entities ? > > On 22 jan, 14:21, Jason Meckley <[email protected]> wrote: > > > your query is a projection, not entity retrieval, so there is no > > reason to set the distinct root entity transformer. If this data maps > > to a dto class than you can do this. > > [HQL] > > select new MyDto(b.Name, avg(u.Rating) as Expr1, b.Scrapped, > > b.DateCreated) > > from BuzzGallery b > > join b.BuzzGalleryArt a > > join a.BuzzUserArt u > > group by b.Name, b.Scrapped, b.DateCreated > > having b.Scrapped = 0 > > order by avg(u.Rating) desc, b.DateCreated desc > > > [Criteria] > > var average = Projections.Avg("u.Rating"); > > var fields = Projections.List() > > .Add(Projections.GroupProperty("Name")) > > .Add(average) > > .Add(Projections.GroupProperty("Scrapped")) > > .Add(Projections.GroupProperty("DateCreated")); > > var results = session. > > .CreateCritera<BuzzGallery>() > > .CreateAlias("BuzzGalleryArt", "a") > > .CreateAlias("a.BuzzUserArt", "u") > > .AddOrder(Order.Desc(average)) > > .AddOrder(Order.Desc("DateCreated")) > > .SetProjection(fields) > > .Add(Restrictions.Eq("Scrapped",0)) > > .SetResultTransformer(Transformers.EntityToBeanCtor) > > .List<MyDto>(); > > > where MyDto looks like this > > public class MyDto > > { > > public MyDto(string name, decimal average, int scrapped, > > DateTime dateCreated) > > { > > Name = name; > > Average = average; > > Scrapped = scrapped; > > DateCreated = dateCreated; > > } > > > public string Name {get; private set;} > > public decimal Average {get; private set;} > > public int Scrapped {get; private set;} > > public DateTime DateCreated {get; private set;} > > > } > > > On Jan 22, 5:59 am, graphicsxp <[email protected]> wrote: > > > > Hi, > > > > I need to write with the NH criteria API, the equivalent of this SQL > > > query : > > > > SELECT DISTINCT dbo.BuzzGallery.Name, AVG(dbo.BuzzUserArt.Rating) AS > > > EXPR1, dbo.BuzzGallery.Scrapped, dbo.BuzzGallery.DateCreated > > > FROM dbo.BuzzGallery INNER JOIN > > > dbo.BuzzGalleryArt ON > > > dbo.BuzzGallery.BuzzGalleryID = dbo.BuzzGalleryArt.BuzzGalleryID INNER > > > JOIN > > > dbo.BuzzUserArt ON > > > dbo.BuzzGalleryArt.BuzzUserArtID = dbo.BuzzUserArt.BuzzUserArtID > > > GROUP BY dbo.BuzzGallery.BuzzGalleryID, dbo.BuzzGallery.Name, > > > dbo.BuzzGallery.Scrapped, dbo.BuzzGallery.DateCreated > > > HAVING (dbo.BuzzGallery.Scrapped = 0) > > > ORDER BY EXPR1 DESC, dbo.BuzzGallery.DateCreated DESC > > > > The diagram can be seen here:http://buzzmyart.s3.amazonaws.com/diag.jpg > > > > So far, I've got the following. Note that I don't know how to > > > integrage the AVG part. Can someone help ? > > > > ICriteria criteria = SessionManager.GetCurrentSession > > > ().CreateCriteria(typeof(BuzzGallery)); > > > > criteria.SetResultTransformer(new > > > DistinctRootEntityResultTransformer()); > > > > criteria.AddOrder(Order.Desc("DateCreated")); > > > return criteria.SetCacheable(true).List<BuzzGallery>(); > > > > Thanks -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
