help with hql

JanLimpens · **Joined:** Tue Sep 25, 2007 6:43 am **Posts:** 16

hello,

I want to translate some native sql to hql, but it's a bit tricky (for me)...

the sql is like that:

Code:

SELECT     Product.*
   FROM         Category 
      INNER JOIN ProductCategory ON Category.CategoryID = ProductCategory.CategoryID 
      INNER JOIN Product ON ProductCategory.ProductID = Product.ProductID
   WHERE     (Category.CategoryID = :cid)

It's a bit more verbose than necessary, but so you can see all relations.

on the domain side Product has a Categories property that is mapped as a many-to-many bag, but Categories does not have a Products mapping (Categories only know about themselves).

Any idea how to state this in hql?

Many thanks!

jgalzic@gmail.com · **Joined:** Tue Apr 25, 2006 1:17 pm **Posts:** 19

Assuming that Category is an association available through Product, you can try something like:

<query name="findMyProduct"><![CDATA[
from
Product prod
inner join
prod.category cat
where
cat.id = :catId and
prod.id = :prodId
]]></query>

where you would load this query by name from the session and set the category and product ids values.

The other thing to do is to turn on sql statements via show_sql properity (and also set format_sql to true) and it should help you in seeing what kind of joins are generated with your queries. Then it's just a matter of discovering which joins aren't matching the SQL you want.

Remember that since Product and Category have associations with each other, that association is expressed through your object graph and will resort to joins naturally with hibernate which is more implicit when you are hand writing your SQL statements.

Hope this helps,
Justin

JanLimpens · **Joined:** Tue Sep 25, 2007 6:43 am **Posts:** 16

Hi Justin,

First of all many thanks for replying!

Unfortunately, the query cannot look like that, because a product has any number of categories and my best bet

Code:

 from
Product prod
inner join
prod.Categories cat
where
cat.id = :cid"

returns with an ArgumentException

Quote:

The value "System.Object[]" is not of type "Product" and cannot be used in this generic collection.
Parameter name: value

I'll keep your logging tips in mind for the time, when I got logging to work again :) (I know I should fix this to start with, but there is so much to do...!)

--Jan

wolli · **Posted:** Wed Mar 05, 2008 3:37 pm

Can you post your mappings and the code for executing the named query ?

JanLimpens · **Joined:** Tue Sep 25, 2007 6:43 am **Posts:** 16

Hello,

sure!

The mapping is as following. Category has no mapping to Product

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                            assembly="Businesslayer"
                            namespace="Businesslayer">
   <class name="Product"
             table="Product"
             lazy="true">
      <id name="Id"
            column="ProductID"
            unsaved-value="0">
         <generator class="identity" />
      </id>
      <property name="Name"
                     column="Name"/>
      <bag name="Categories"
             table="ProductCategory">
         <key column="ProductID"/>
         <many-to-many class="Category"
                              column="CategoryID"/>
      </bag>
   </class>
</hibernate-mapping>

The code looks as following (the not working hql version is commented out)

Code:

//        public IList<Product> GetByCategory(int categoryId)
//        {
//            var hql = @"
//                   from
//Product prod
//inner join
//prod.Categories cat
//where
//cat.id = :cid";
//            return NHibernateSession.CreateQuery(hql)
//                .SetInt32("cid", categoryId)
//                .List<Product>();
//        }

      public IList<Product> GetByCategory(int categoryId)
      {
         var sql = @"
                  SELECT     Product.*
                  FROM         Category INNER JOIN
                                   ProductCategory ON Category.CategoryID = ProductCategory.CategoryID INNER JOIN
                                   Product ON ProductCategory.ProductID = Product.ProductID
                  WHERE     (Category.CategoryID = :cid)";
         return NHibernateSession.CreateSQLQuery(sql)
            .AddEntity(typeof(Product))
            .SetInt32("cid", categoryId)
            .List<Product>();
      }   
   }

wolli · **Posted:** Wed Mar 05, 2008 4:47 pm

I can't see any obvious error in the hql. Can you fix logging and send the sql statemens ?

I found something in the documentation:

http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/queryhql.html#queryhql-where

Code:

from Product prod where prod.Categories.id = :cid

Assuming that you join on the identity of Category, this query should work without a join, but I don't know, if that's working with many-to-many.

Can you try the query as criteria:

Code:

ICriteria criteria = NHibernateSession.CreateCriteriy(typeof(Product))
         .CreateCriteria("Categories")
         .Add( Expression.Eq("CategoryId", categoryId))
         .List<Product>();

jgalzic@gmail.com · **Joined:** Tue Apr 25, 2006 1:17 pm **Posts:** 19

If you have multiple categories per product, you can also use a parameter list for your HQL as such:

"from Product prod inner join prod.Categories cat where cat in (:cat_list)"

and then with your query object, you can do:
query.setParameterList("cat_list", categoryList);
List result = query.list();

where categoryList would be the list of category objects you want to narrow your results with..

JanLimpens · **Joined:** Tue Sep 25, 2007 6:43 am **Posts:** 16

Very cool, Wolfgang!

Code:

      public IList<Product> GetByCategory(int categoryId)
      {
         return NHibernateSession.CreateCriteria(typeof(Product))
          .CreateCriteria("Categories")
          .Add(Expression.Eq("Id", categoryId))
          .List<Product>();
      }

Did work!

Thanks a bunch!

Jan