HQL query with joins to Criteria query (Bug?)

MisterTom_ · **Joined:** Tue May 15, 2007 10:10 am **Posts:** 8

Hi,

I need help to translate a HQL query to a QBC query.
Here is the (working) HQL query :

Code:

select user from User user inner join user.Bids bid inner join bid.Item item where item.Name like '%Color%'

Now here is my QBC query :

Code:

ICriteria criteria = session.CreateCriteria(typeof(User))
   .CreateCriteria("Bids", JoinType.InnerJoin)
   .CreateCriteria("Item", JoinType.InnerJoin)
   .Add(Expression.Like("Name", "%Color%").IgnoreCase());

The generated SQL does not contain the second inner join in the criteria version. Is there anything wrong in my query ?

Hibernate version:
1.2.0.3002

Mapping documents:
I can include if needed

Full stack trace of any exception that occurs:

Code:

NHibernate.ADOException: could not execute query
[see generated SQL] ---> Oracle.DataAccess.Client.OracleException ORA-00904: "ITEM2_"."NAME": invalid identifier    à Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
   à Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src)
   à Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
   à Oracle.DataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
   à System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   à NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
   à NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
   à NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
   à NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
   à NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
   --- Fin de la trace de la pile d'exception interne ---
   à NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
   à NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters)
   à NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
   à NHibernate.Loader.Criteria.CriteriaLoader.List(ISessionImplementor session)
   à NHibernate.Impl.SessionImpl.Find(CriteriaImpl criteria, IList results)
   à NHibernate.Impl.SessionImpl.Find[T](CriteriaImpl criteria)
   à NHibernate.Impl.CriteriaImpl.List[T]()
   à NHibernate.Impl.CriteriaImpl.Subcriteria.List[T]()

The generated SQL (show_sql=true):

Code:

SELECT this_.ID        as ID3_1_,
       this_.FIRSTNAME as FIRSTNAME3_1_,
       this_.LASTNAME  as LASTNAME3_1_,
       this_.AGE       as AGE3_1_,
       this_.APP_ID    as APP6_3_ 1_,
       this_.RATE      as RATE3_1_,
       this_.STREET    as STREET3_1_,
       this_.CITY      as CITY3_1_,
       this_.ZIPCODE   as ZIPCODE3_1_,
       this_.COUNTRY   as COUNTRY3_1_,
       this_.USER      LEVEL as USERLEVEL3_1_,
       this_.TYPE      as TYPE1_,
       bid1_.USERID    as USERID8_0_,
       bid1_.ITEMID    as ITEMID8_0_,
       bid1_.BIDDATE   as BIDDATE8_0_,
       bid1_.AMOUNT    as AM OUNT8_0_
  FROM USERS this_, BIDS bid1_
 WHERE this_.ID = bid1_.USERID
   AND lower(item2_.NAME) like :p0;

:p0 = '%color%'

sethrohit1977 · **Joined:** Mon Jan 22, 2007 10:32 am **Posts:** 101

Not sure what could be wrong. Try adding aliases, something like

ICriteria criteria = session.CreateCriteria(typeof(User))
.CreateCriteria("Bids", "bid", JoinType.InnerJoin)
.CreateCriteria("bid.Item", "item", JoinType.InnerJoin)
.Add(Expression.Like("item.Name", "%Color%").IgnoreCase());

MisterTom_ · **Joined:** Tue May 15, 2007 10:10 am **Posts:** 8

Thank you for your answer.

To be honest I tried many, many possibilities as if I wanted to demonstrate the uselessness of coincidence programming.
So I already tried to call CreateCriteria with aliases, I also tried to call CreateAlias because I saw it was possible with hibernate (http://forum.hibernate.org/viewtopic.php?t=981880&highlight=criteria+outer+join).

I copied an example from NHibernate tests and it doesn't work either :

Code:

DetachedCriteria dc3 = DetachedCriteria.For(typeof(Student), "st")
   .CreateCriteria("Enrolments")
   .CreateCriteria("Course")
   .Add(Expression.Property.ForName("Description").Eq("Hibernate Training"))
   .SetProjection(Expression.Property.ForName("st.Name"));

I don't think my mapping is wrong because the HQL version -- and all other queries -- works fine.

sethrohit1977 · **Joined:** Mon Jan 22, 2007 10:32 am **Posts:** 101

I am using similar criteria queries in my project and they work fine, only difference being that I am using Hibernate not NHibernate. If possible, post your mappings, i would like to try it out, may be something to do with NHibernate.

MisterTom_ · **Joined:** Tue May 15, 2007 10:10 am **Posts:** 8

Here are the (simplified) mapping files.

User.hbm.xml

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="OrmTests.NHibernate" namespace="OrmTests.DomainModel.NHibernate">

   <class name="User" table="USERS" discriminator-value="0">

      <id name="Id" column="ID" type="Int64">
         <generator class="seqhilo">
            <param name="sequence">USER_ID_SEQ</param>
            <param name="max_lo">100</param>
         </generator>
      </id>

      <discriminator column="TYPE" type="Byte" length="1"/>

      <bag name="Bids" inverse="true" fetch="join">
         <key column="USERID"/>
         <one-to-many class="Bid"/>
      </bag>

      <subclass name="BasicUser" discriminator-value="1">
      </subclass>

      <subclass name="PremiumUser" discriminator-value="2">
      </subclass>

   </class>

</hibernate-mapping>

Bid.hbm.xml

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="OrmTests.NHibernate" namespace="OrmTests.DomainModel.NHibernate">
   
   <class name="Bid" table="BIDS">
      
      <composite-id>
         <key-many-to-one name="User" class="User" column="USERID"/>
         <key-many-to-one name="Item" class="Item" column="ITEMID"/>
         <key-property name="Date" type="DateTime" column="BIDDATE"/>
      </composite-id>
      
      <property name="Amount" column="AMOUNT" type="Decimal"/>
      
   </class>
   
</hibernate-mapping>

Item.hbm.xml

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="OrmTests.NHibernate" namespace="OrmTests.DomainModel.NHibernate">
   
   <class name="Item" table="ITEMS">
      
      <id name="Id" column="ID" type="Int64">
      <generator class="seqhilo">
         <param name="sequence">ITEM_ID_SEQ</param>
            <param name="max_lo">100</param>
         </generator>
      </id>
      
      <property name="Name" column="NAME" type="String"/>

      <bag name="Bids" inverse="true">
         <key column="ITEMID"/>
         <one-to-many class="Bid"/>
      </bag>
      
      <joined-subclass name="CD" table="CDS">
      </joined-subclass>
      
      <joined-subclass name="Book" table="BOOKS">
      </joined-subclass>
      
   </class>

</hibernate-mapping>

MisterTom_ · **Joined:** Tue May 15, 2007 10:10 am **Posts:** 8

Ok I found something. Here is the code from JoinWalker.cs :

Code:

      /// <summary>
      /// For an entity class, add to a list of associations to be fetched
      /// by outerjoin
      /// </summary>
      private void WalkEntityTree(
         IOuterJoinLoadable persister,
         string alias,
         string path,
         int currentDepth)
      {
         int n = persister.CountSubclassProperties();
         for (int i = 0; i < n; i++)
         {
            IType type = persister.GetSubclassPropertyType(i);
            if (type.IsAssociationType)
            {
               WalkEntityAssociationTree(
                  (IAssociationType) type,
                  persister,
                  i,
                  alias,
                  path,
                  persister.IsSubclassPropertyNullable(i),
                  currentDepth
                  );
            }
            else if (type.IsComponentType)
            {
               WalkComponentTree(
                  (IAbstractComponentType) type,
                  i,
                  0,
                  persister,
                  alias,
                  SubPath(path, persister.GetSubclassPropertyName(i)),
                  currentDepth
                  );
            }
         }
      }

WalkEntityTree will never find the path "Bids.Item" because Item is not a property (although it is a property in the C# sense), it is a key-many-to-one and so it cannot be returned by persister.CountSubclassProperties() or persister.GetSubclassPropertyType(i).

I think than CreateCriteria doesn't work to join with types that are part of the primary key. I fixed JoinWalker.cs and it seems to work now.