Hi all,
This is my first message in this list.
I've a problem with a HQL query: in my model I have two entities,
Categories and Courses. Each Category have a collection of categories
and a collection of courses:
public class Category
{
public virtual int Id { get; set; }
public virtual Category ParentCategory { get; set; }
public virtual Site Site { get; set; }
public virtual string Name { get; set; }
public virtual string ImageFile { get; set; }
public virtual IList<Content> Contents { get; set; }
public virtual IList<Course> Courses { get; set; }
}
Well, I need to select all root categories and count all active and
visible courses for each category. I ended up with the following HQL query:
select cat, count(elements(co))
from Category cat left join cat.Courses co
where cat.ParentCategory is null
and co.Visible=true and co.Active=true
group by cat.Id, cat.ParentCategory,
cat.Site, cat.Name, cat.ImageFile
order by cat.Name asc
This works as expected but only returns categories having one or more
courses. I need to get the categories with no courses (count = 0)
aswell, but I've tried different options without success. The problem
seems to be in the where clause. If I remove the "co.Visible=true and
co.Active=true" part then I get the categories with courses count=0
aswell, but I need to count only the active and visible courses. Does
somebody knows how to get these categories with the courses count
including the records where count is 0?
FYR, the above HQL translates into this SQL:
select category0_.id as col_0_0_,
count(courses1_.id) as col_1_0_,
category0_.id as id99_,
category0_.name as name99_,
category0_.imageurl as imageurl99_,
category0_.parentid as parentid99_,
category0_.siteid as siteid99_
from vt_categories category0_
left outer join vt_courses courses1_
on category0_.id = courses1_.categoryid
where category0_.parentid is null
and courses1_.visible = 1
and courses1_.active = 1
group by category0_.id,
category0_.parentid,
category0_.siteid,
category0_.name,
category0_.imageurl
order by category0_.name asc
Thank you in advance
--
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.
