CODEWITHSUNDEEP
nhibernatejoincount
PatachouNewG
PatachouNewG

Reputation: 35

NHibernate left join select count in one-to-many relationship

I've been looking for a week after a correct synthax whithout success.

I have 2 classes :

public class ArtworkData
{
      public virtual Guid Id { get; set; }
      public virtual string Name { get; set; }    
      public virtual IList<CommentData> Comments { get; set; }
}
public class CommentData
{
      public virtual Guid Id { get; set; }
      public virtual string Text { get; set; }
      public virtual ProfileData Profile { get; set; }
      public virtual ArtworkData Artwork { get; set; }
      public virtual DateTime Created { get; set; }
}

I want to do this query : 

    SELECT   this_.ArtworkId          as ArtworkId3_3_,
         this_.Name               as Name3_3_,
         this_.Description        as Descript3_3_3_,
    FROM     Artwork this_
         LEFT outer JOIN 
   (SELECT c.ArtworkIdFk, count(1) Cnt
   FROM Comment c
   GROUP BY c.ArtworkIdFk) as com
   on com.ArtworkIdFk = this_.ArtworkId
    ORDER BY 1 desc

But I don't find the way to. At this moment I just have something like this : 

ICriteria c = this.Session.CreateCriteria(typeof(ArtworkData));
if(filter.Category !=null)
{
      c.CreateAlias("Categories", "cat")
       .Add(Restrictions.Eq("cat.Id", filter.Category.Id));
}
DetachedCriteria crit = DetachedCriteria.For(typeof(CommentData), "comment")
                    .SetProjection(Projections.ProjectionList()
                    .Add(Projections.Count("comment.Id").As("cnt"))    
                .Add(Projections.GroupProperty("comment.Artwork.Id")));                    



c.Add(Expression.Gt(Projections.SubQuery(crit), 0));
   c.AddOrder(Order.Desc(Projections.SubQuery(crit)));

But it's not what I want. I want to get all Artworks order by the number of comments (but I don't need to get this number). Please help me! I'm going crazy!

Upvotes: 2

Views: 1228

Answers (3)

gor
gor

Reputation: 11658

Try Detached Criteria. Take a look at this blogpost.

Upvotes: 1

gor
gor

Reputation: 11658

If you use NHibernate 3 you could use this code:

var artworks = Session.Query<Artwork>().OrderBy(a => Comments.Count);

Or you could use HQL:

Session.CreateQuery("from Artwork a order by size(a.Comments)")

Upvotes: 1

Chingiz Musayev
Chingiz Musayev

Reputation: 2962

I don't understand what are you trying to do with this weird SQL but if you need to get all Artworks with number of comments you can try this query:

<query name="ArtworkWithCommentsCount">
   SELECT artwork.Name, artwork.Comments.size
   FROM Artwork artwork
</query>

Upvotes: 1

Related Questions