Databases
StackOverflow2013
Postsdbo
PONHibernate left join select count in one-to-many relationship
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONHibernate left join select count in one-to-many relationship
    text
    copied!<p>I've been looking for a week after a correct synthax whithout success.</p> <p>I have 2 classes :</p> <pre><code>public class ArtworkData { public virtual Guid Id { get; set; } public virtual string Name { get; set; } public virtual IList&lt;CommentData&gt; 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; } } </code></pre> <p>I want to do this query : </p> <pre><code> 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 </code></pre> <p>But I don't find the way to. At this moment I just have something like this : </p> <pre><code>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))); </code></pre> <p>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!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload