Query items that have at least one element...

rolandz · **Posted:** Wed May 09, 2007 2:31 am

Hi,

I have an Article and ArticleCategory classes. The article has a collection of assigned categories (unidirectional many-to-many association). The question is how I can query all articles that are assigned to at least one category from categories being provided.

I would like to have the method in my DAO class:

Code:

IList<Article> GetItemsByCategory(params ArticleCategory[] categories)
{
  // How does the query need to look like?
  string query = ...

  // Passing query to the Spring.Net generic hibernate template that executes it.
  return HibernateTemplate.Find<Article>(query, categories);
}

There's my Article mapping:

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
   <class name="MyWebPage.Core.Domain.Article, MyWebPage.Core" table="Article">
      <id name="Id" type="Int32">
         <generator class="native" />
      </id>
      <property name="DateCreated" type="DateTime" not-null="true" />
      <property name="Title" type="String(50)" not-null="true" />
      <property name="Shortcut" type="String(500)" not-null="true" />
      <property name="Content" type="String(10000)" not-null="true" />
      <bag name="Categories" generic="true" table="Article_ArticleCategory">
         <key />
         <many-to-many class="MyWebPage.Core.Domain.ArticleCategory, MyWebPage.Core" />
      </bag>
      <list name="Comments" generic="true" table="ArticleComment">
         <key />
         <index />
         <composite-element class="MyWebPage.Core.Domain.ArticleComment, MyWebPage.Core">
            <property name="DateCreated" type="DateTime" not-null="true" />
            <property name="CreatedBy" type="String(20)" not-null="true" />
            <property name="Title" type="String(50)" not-null="true" />
            <property name="Content" type="String(1000)" not-null="true" />
         </composite-element>
      </list>
   </class>
</hibernate-mapping>

as well as ArticleCategory mapping:

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
   <class name="MyWebPage.Core.Domain.ArticleCategory, MyWebPage.Core" table="ArticleCategory">
      <id name="Id" type="Int32">
         <generator class="native" />
      </id>
      <property name="Parent" type="MyWebPage.Core.Domain.ArticleCategory, MyWebPage.Core" />
      <property name="DateCreated" type="DateTime" not-null="true" />
      <property name="Name" type="String(50)" not-null="true" />
      <property name="Description" type="String(1000)" />
   </class>
</hibernate-mapping>

Thanks for any hints...

canton · **Posted:** Wed May 09, 2007 4:11 am

I would use EXSISTS expression in your scenario. There is an example in NHibernate Documentation: http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/queryhql.html#queryhql-subqueries

rolandz · **Posted:** Fri May 11, 2007 4:01 am

canton wrote:

I would use EXSISTS expression in your scenario. There is an example in NHibernate Documentation:
http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/queryhql.html#queryhql-subqueries

For now I've reduced my interface so I can query only for items assigned to just one category. I simply can't imagine how to process an array parameter to query for multiple categories...

My current query looks like the following:

Code:

select distinct a from Article a inner join a.Categories where ? in elements(a.Categories)

canton · **Posted:** Fri May 11, 2007 4:32 am

How about this HQL query?

Code:

FROM Article article
WHERE EXISTS (
   FROM ArticleCategory category
   WHERE category IN (:categories)
   AND category IN elements(article.Categories))

Beware that the query was not tested.