sql-query many to many

NecroFilja · **Posted:** Tue Jan 26, 2010 5:03 am

Hello All,

I am not so exprirenced in Hibernate. So following issue appeared:
I need to select Object with its childs in relation many to many. xml for child works good. But could not produce valid for parent have tried different variants. without success. Faiiled just on parse stage (pos.*, pos.element.*, pos.Id all variants :((( )

Child, Parent classes

Code:

  public class ContactEntity
   {
      #region Members
      private int _id;
      private string _lastName;
        private string _firstName;
        private PositionEntity [] _positions;
      #endregion   //   Members

      #region Public Properties
      public virtual int Id
      {
         get { return _id; }
         set { _id = value; }
      }

        public virtual string LastName
      {
         get { return _lastName; }
         set { _lastName = value; }
      }

        public virtual string FirstName
      {
         get { return _firstName; }
         set { _firstName = value; }
      }

        public virtual PositionEntity[] Positions
      {
         get { return _positions; }
         set { _positions = value; }
      }
      #endregion   //   Public Properties
    }

 public class PositionEntity
    {
        #region Members
        private int _id;
        private string _name;
        #endregion

        #region Public Properties
        public virtual int Id
        {
            get { return _id; }
            set { _id = value; }
        }

        public virtual string Name
        {
            get { return _name; }
            set { _name = value; }
        }
        #endregion
    }

xml file child

Code:

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

   <class name="DAL.Contacts.PositionEntity"  table="TBL_POSITION" >
      <id name="Id" type="Int32" column="POSITION_ID">
         <generator class="identity"/>
      </id>
      <property name="Name" column="IPOSITION_NAME" type="String"/>
   </class>
</hibernate-mapping>

xml file parent

Code:

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

   <class name="ContactEntity"  table="TBL_CONTACT" >
      <id name="Id" type="Int32" column="CONTACT_ID">
         <generator class="identity"/>
      </id>
      <property name="LastName" column="LAST_NAME" type="String"/>
      <property name="FirstName" column="FIRST_NAME" type="String"/>
      <bag name="Positions" table="TBL_CONTACT_POSITION" lazy="false" >
         <key column="CONTACT_ID"></key>
         <many-to-many class="PositionEntity" column="POSITION_ID"></many-to-many>
      </bag>
   </class>

   <sql-query name="ContactEntity.AvailableContacts">
      <return alias="con" class="ContactEntity"/>
      <return-join alias="pos" property="con.Positions"/>
      SELECT   con.CONTACT_ID {con.Id},
      con.LAST_NAME {con.LastName},
      con.FIRST_NAME {con.FirstName},
      {pos.*}
   

      FROM TBL_CONTACT con
      LEFT JOIN
      TBL_CONTACT_POSITION con_pos on con_pos.CONTACT_ID = con.CONTACT_ID
      LEFT JOIN
      TBL_POSITION pos ON pos.POSITION_ID = con_pos.POSITION_ID

      WHERE (FIRST_NAME + LAST_NAME) LIKE :contactName
   </sql-query>
</hibernate-mapping>

wolli · **Posted:** Thu Jan 28, 2010 4:38 am

Probally {pos.*} is not valid if you want to use it for mapping. You have to specify each field separately.

NecroFilja · **Posted:** Thu Jan 28, 2010 8:25 am

Have tried indvidual fields also.
Seem to me NHibernate doesn't support.
Following statement in NHibernate sources seem to me specify that it will not work (but in java it works!!!):
SQLQueryReturnProcessor.cs

Code:

private void AddCollection(string role, string alias, IDictionary<string, string[]> propertyResults)
      {
         ISqlLoadableCollection collectionPersister = (ISqlLoadableCollection) Factory.GetCollectionPersister(role);
         alias2CollectionPersister[alias] = collectionPersister;
         string suffix = GenerateCollectionSuffix();
         log.Debug("mapping alias [" + alias + "] to collection-suffix [" + suffix + "]");
         alias2CollectionSuffix[alias] = suffix;
         collectionPropertyResultMaps[alias] = propertyResults;

         [b]if (collectionPersister.IsOneToMany)
         {
            ISqlLoadable persister = (ISqlLoadable) collectionPersister.ElementPersister;
            AddPersister(alias, Filter(propertyResults), persister);
         }[/b]
      }

wolli · **Posted:** Thu Jan 28, 2010 9:01 am

In the documentation there are examples with something like {pers.*}, so it should work. I can remember that I also had some trouble until I got my statements to work. What is the exact error message and does the exception contain a inner exception with some more information ? How do you use the query ? session.GetNamedQuery(..) ?

NecroFilja · **Posted:** Thu Jan 28, 2010 12:10 pm

for {pos.*}

NullReferenceException. object reference not set...
ISqlLoadable persister = context.GetEntityPersisterByAlias(aliasName);
persister us null for many-to-many join

Code:

"   at NHibernate.Loader.Custom.Sql.SQLQueryParser.ResolveProperties(String aliasName, String propertyName)\r\n   at NHibernate.Loader.Custom.Sql.SQLQueryParser.ResolveCollectionProperties(String aliasName, String propertyName)\r\n   at NHibernate.Loader.Custom.Sql.SQLQueryParser.SubstituteBrackets()\r\n   at NHibernate.Loader.Custom.Sql.SQLQueryParser.Process()\r\n   at NHibernate.Loader.Custom.Sql.SQLCustomQuery..ctor(INativeSQLQueryReturn[] queryReturns, String sqlQuery, ICollection`1 additionalQuerySpaces, ISessionFactoryImplementor factory)\r\n   at NHibernate.Engine.Query.NativeSQLQueryPlan..ctor(NativeSQLQuerySpecification specification, ISessionFactoryImplementor factory)\r\n   at NHibernate.Engine.Query.QueryPlanCache.GetNativeSQLQueryPlan(NativeSQLQuerySpecification spec)\r\n   at NHibernate.Impl.SessionFactoryImpl.CheckNamedQueries()\r\n   at NHibernate.Impl.SessionFactoryImpl..ctor(Configuration cfg, IMapping mapping, Settings settings, EventListeners listeners)\r\n   at NHibernate.Cfg.Configuration.BuildSessionFactory()\r\n   at "

it is generated when trying to build session factory.

works fine for many-to-one return-join {address.*} in the same query (when removing many-to-many).
Seem to me samples was for one-to-many

wolli · **Posted:** Fri Jan 29, 2010 3:20 am

Damn, didn't see that the mapping contains a many-to-many. One question first, do you really need a custom sql for that. May be just a filter would be enough and much easier to do ?

I just found this, so probablly it's not possible:

http://nhjira.koah.net/browse/NH-925

NecroFilja · **Posted:** Fri Jan 29, 2010 4:00 am

wolli wrote:

Damn, didn't see that the mapping contains a many-to-many. One question first, do you really need a custom sql for that. May be just a filter would be enough and much easier to do ?

I just found this, so probablly it's not possible:

http://nhjira.koah.net/browse/NH-925

Custom sql query I think required. Because I need to select Contacts and its Positions, Address. But when selecting I need to serahc to another 5 tables to filter by permissions profiles etc. And I don't need to have this objects in my object model (they are auxiliary). On current moment I have stopped on following solution (where query is just simplified really there are a lot of other exists, in etc...):

Code:

<sql-query name="ContactEntity.AvailableContacts">
      <return alias="con" class="ContactEntity"/>
      <return-join alias="addr" property="con.Address"/>
      SELECT   con.CONTACT_ID {con.Id},
      con.LAST_NAME {con.LastName},
      con.FIRST_NAME {con.FirstName},
      con.ADDRESS_ID {con.Address},
      {addr.*},
      {pos.*}
   

      FROM TBL_CONTACT con
      LEFT JOIN
      TBL_ADDRESS adr on adr.ADDRESS_ID = con.ADDRESS_ID

      WHERE (FIRST_NAME + LAST_NAME) LIKE :contactName
   </sql-query>

So addresses are returned by return join. But positions unfortunatelly by separate query for each contact. From performance point of view this is not good. But no other way.

Also regarding described issue. There is statement that in Hibernate it doesn't work also. It is not true. I have discussed with one java-hibernate guy. He said I am frequently using such technique without any problems. So NHibernate issue...