Help with projection

thequestioningdev · **Joined:** Tue Feb 03, 2009 12:53 am **Posts:** 2

Hi All,

I have a simple scenario that I'm finding difficult to get working - I have two classes, Article and Comment:

public class Article
{
public Guid Id { get; set; }
public string Title { get; set; }
public ICollection<Comment> Comments { get; set; }
// etc..
}

public class Comment
{
public Guid Id { get; set; }
public DateTime CreatedAt { get; set; }
public Article Article { get; set; }
public string Text { get; set; }
}

Comment has a many-to-one relationship with Article.

I'd like to populate a list of all Articles Id's, Titles and the last comment that was associated with the article, if any (the text associated with the comment, selected by the most recent CreatedAt date grouped by article) so that I could then populate the following DTO:

public class ArticleDTO
{
public ArticleDTO(Guid id, string title, string lastComment) { ... }
}

Is this something I could use a sub query to achieve? I've tried a few different approaches, but I cant seem to get the correct results. Just some pointers in the right direction would be helpful, as I'm not sure where to start at the moment.

wolli · **Posted:** Tue Feb 03, 2009 4:39 am

Try this:

Code:

select a.Id, a.Title, c2.CreatedAt
from Article a left join fetch a.Comments c1
where c1.CreatedAt = (select max(c2.CreatedAt) from Comments c2 where c2.Article = a)

For the population of the DTO you can use a resultset transformer (I'm not 100% sure if it works with HQL queries , because normally it is used with SQL queries):

Code:

query.SetResultTransformer(Transformers.AliasToBean(typeof(ArticleDTO )))

Where the projected properties must match properties of the DTO (meaninig you need an Id, a Title and a CreatedAt property).

Or with NH 2.0 you could do something like:

Code:

select new ArticeDTO(a.Id, a.Title, c2.CreatedAt)
from Article a left join fetch a.Comments c1
where c1.CreatedAt = (select max(c2.CreatedAt) from Comments c2 where c2.Article = a)

thequestioningdev · **Joined:** Tue Feb 03, 2009 12:53 am **Posts:** 2

wolli wrote:

Try this:

Code:

select a.Id, a.Title, c2.CreatedAt
from Article a left join fetch a.Comments c1
where c1.CreatedAt = (select max(c2.CreatedAt) from Comments c2 where c2.Article = a)

That worked great, I ended up converting it to use ICriteria (because additional criteria is coming from a specification to provide additional filtering) - so I ended up with:

DetachedCriteria maxCreateDateForComments = DetachedCriteria.For<Comment>("c2")
.Add(Restrictions.EqProperty("c2.Article", "c1.Article"))
.SetProjection(Projections.Max("c2.CreatedAt"));

DetachedCriteria criteria = DetachedCriteria.For<Article>("a")
.CreateAlias("Comments", "c1", JoinType.LeftOuterJoin)
.Add(Restrictions.Or(
Subqueries.PropertyEq("c1.CreatedAt", maxCreateDateForComments),
Restrictions.IsEmpty("a.Comments")));

Thanks for the help!