I have two entities (Articles and Categories) that have a many to many association between them. In order to be able to show related articles to users, I'm trying to write a query that will return recent articles that belong to one of the categories that the current article is in. Writing the sql to do this is straight forward enough but I can't seem to get the HQL right.
Code:
public IList<Article> GetRelatedArticles(Article relatedArticle, int count)
{
IQuery query =
session.CreateQuery(
@"select article from Article article
join article.Categories category
where article.Publish = 1 and article.PublishDate < :now
and category in (select relatedArticle.Categories from Article relatedArticle where relatedArticle = :relatedArticle)
order by article.PublishDate DESC")
.SetDateTime("now", DateTime.Now)
.SetEntity("relatedArticle", relatedArticle)
.SetMaxResults(count);
return query.List<Article>();
}
When I run this query I get an NHibernate exception with the following message.
"unindexed collection before [] [select article from AroundTheRings.Model.Article article
join article.Categories category
where article.Publish = 1 and article.PublishDate < :now
and category in (select relatedArticle.Categories from AroundTheRings.Model.Article relatedArticle where relatedArticle = :article)
order by article.PublishDate DESC]."
Can someone shed some light on what I'm doing wrong here?