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.
