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.
