Works like a treat ! Thanks a lot :) On 22 jan, 23:25, Jason Meckley <[email protected]> wrote: > <property name="AverageRating" formula="( > SELECT AVG(a.Rating) > FROM dbo.BuzzGalleryArt a > INNER JOIN dbo.BuzzUserArt u ON a.BuzzUserArtID > = u.BuzzUserArtID > INNER JOIN dbo.BuzzGallery g ON a.BuzzGalleryID > = g.BuzzGalleryID > WHERE u.Scrapped = 0 > AND a.Rating > 0 > AND a.BuzzGalleryID = BuzzGalleryID)"/> > > should do the trick. by aliasing the tables in the subquery we can > compare the sub-tables id to the parent-table's id > > On Jan 22, 12:28 pm, graphicsxp <[email protected]> wrote: > > > > > Going the formula road seems to be the right thing to do. > > > However, I can't figure out how to do it. I'm almost there but not > > quite. Here's my formula : > > > <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"> > > <class name="BuzzArt.BuzzGallery, BuzzArt" table="BuzzGallery" > > where="Scrapped=0"> > > > <id name="Id" column="BuzzGalleryID" type="guid"> > > <generator class="guid.comb"/> > > </id> > > > <property name="AverageRating" formula="(SELECT AVG > > (dbo.BuzzUserArt.Rating) > > FROM dbo.BuzzGalleryArt > > INNER JOIN dbo.BuzzUserArt ON > > dbo.BuzzGalleryArt.BuzzUserArtID = dbo.BuzzUserArt.BuzzUserArtID > > INNER JOIN dbo.BuzzGallery ON > > dboBuzzGalleryArt.BuzzGalleryID = dbo.BuzzGallery.BuzzGalleryID > > WHERE dbo.BuzzUserArt.Scrapped = 0 AND > > dbo.BuzzUserArt.Rating > 0 AND dbo.BuzzGalleryArt.BuzzGalleryID = WHAT > > SHOULD I WRITE HERE ???????????????????????)"></property> > > > </class> > > </hibernate-mapping> > > > As you can see, I don't know how to specify the right Id, which should > > be the current Id of the entity we are trying to get. > > > The final query should look like : > > > SELECT buzzgaller0_.BuzzGalleryID as BuzzGall1_5_0_, > > buzzgaller0_.BuzzUserID as BuzzUserID5_0_, > > buzzgaller0_.Name as Name5_0_, > > buzzgaller0_.DateCreated as DateCrea4_5_0_, > > buzzgaller0_.DateModified as DateModi5_5_0_, > > buzzgaller0_.Scrapped as Scrapped5_0_, > > (SELECT AVG(dbo.BuzzUserArt.Rating) > > 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 > > WHERE dbo.BuzzUserArt.Scrapped = 0 and dbo.BuzzUserArt.Rating> 0 > > and dbo.BuzzGallery.BuzzGalleryID = 'f2299f8a-77a7-4a4e- > > > a969-9cd7005c9a7e') as formula0_0_ > > FROM BuzzGallery buzzgaller0_ > > WHERE buzzgaller0_.BuzzGalleryID = 'f2299f8a-77a7-4a4e- > > a969-9cd7005c9a7e' /* @p0 */ > > and (buzzgaller0_.Scrapped = 0) > > > On 22 jan, 17:18, Jason Meckley <[email protected]> wrote: > > > > 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.
