sql-query returning entity and scalar?

Gradiation · **Joined:** Fri Apr 27, 2007 11:50 am **Posts:** 23

hi guys,

I'm trying to write a sql-query that will bring back a list of hotel entities along with a scalar value representing the distance from a passed in latitude and longitude. I am trying to use the SetResultTransformer method to map the hotel and the scalar value to an un-mapped class (as the distance will always be dynamic)

The problem i am having is i don't seem to be able to return both an entity and a scalar at the same time. I have written a custom IResultTransformer class, but when i debug and look through the tuple paramater it only shows the hotel instance, and not the distance scalar value.

Does anybody know how i can atchieve what i am trying to do?

A sample of my sql is as follows:

Code:

SELECT {Hotel.*}, 10 as Distance FROM Hotel LEFT JOIN Room ON Room.HotelId = Hotel.HotelId WHERE 1=1 AND Room.Capacity BETWEEN :capacity_min AND :capacity_max ORDER BY {Hotel.Holindex} ASC

My search result class is as follows:

Code:

public class SearchResult
    {
        #region Members

        private Hotel _hotel;
        private float _distance;

        #endregion

        #region Properties

        /// <summary>
        /// Gets or sets the hotel.
        /// </summary>
        /// <value>The hotel.</value>
        public Hotel Hotel
        {
            get { return _hotel; }
            set { _hotel = value; }
        }

        /// <summary>
        /// Gets or sets the distance.
        /// </summary>
        /// <value>The distance.</value>
        public float Distance
        {
            get { return _distance; }
            set { _distance = value; }
        }

        #endregion

        #region Constructors

        /// <summary>
        /// Initializes a new instance of the <see cref="SearchResult"/> class.
        /// </summary>
        public SearchResult()
        { }

        /// <summary>
        /// Initializes a new instance of the <see cref="SearchResult"/> class.
        /// </summary>
        /// <param name="hotel">The hotel.</param>
        /// <param name="distance">The distance.</param>
        public SearchResult(Hotel hotel, 
            float distance)
        {
            Hotel = hotel;
            Distance = distance;
        }

        #endregion
    }

My generate sql statement is:

Code:

                IQuery query = NHibernateSession.CreateSQLQuery(sql.ToString())
                .AddEntity("Hotel", typeof(Hotel))
                .SetResultTransformer(new SearchResultTransformer());

And my SearchResultTransformer is currently:

Code:

public class SearchResultTransformer : IResultTransformer
    {
        #region IResultTransformer Members

        public object TransformTuple(object[] tuple, string[] aliases)
        {
            SearchResult result = new SearchResult();
            //for (int i = 0; i < tuple.Length; i++)
            //{
            //    properties[i].SetValue(result, tuple[i], null);
            //}
            return result;
        }

        public IList TransformList(IList collection)
        {
            return collection;
        }

        #endregion
    }

wolli · **Posted:** Thu Aug 28, 2008 5:54 am

Code:

IQuery query = NHibernateSession.CreateSQLQuery(sql.ToString())
                .AddEntity("Hotel", typeof(Hotel))
                .SetResultTransformer(new SearchResultTransformer());

You probably need to add the scalar with AddScalar(), too.

Gradiation · **Joined:** Fri Apr 27, 2007 11:50 am **Posts:** 23

Cool,

I did try that, but i think i was actualy using the built in transformers at the time which were throwing an error.

The distance property does indeed now show in the tuple paramater for the custom IResultTransformer.

Thanks for your help.

Matt

pvradhakrishna · **Joined:** Tue Feb 19, 2008 6:05 pm **Posts:** 82

session.createSQLQuery("SELECT rs1.rs_col1 as " +
" rsCol1 FROM rs_table rs1")
.setResultTransformer(Transformers.aliasToBean(RsTable.class))
.list();

This sql query returns the result as RSCOL1 where as I have a property rsCol1 resulting in a property not found exception? any solution for this problem?

wolli · **Posted:** Tue Oct 21, 2008 5:54 pm

Try

session.createSQLQuery("SELECT rs1.rs_col1 as " +
" rsCol1 FROM rs_table rs1")
.addScalar("rsCol1", <correct type>)
.setResultTransformer(Transformers.aliasToBean(RsTable.class))
.list();

pvradhakrishna · **Joined:** Tue Feb 19, 2008 6:05 pm **Posts:** 82

It worked!

Alright, so let me go back and tell you a little bit more. My motive is to load objects (along with their parent objects) with specific properties dynamically.

Initially I kept adding constructors and it was really a pain to maintain/remember them easily when the number of properties increased and in a case when they are dynamic, everything failed, I asked myself, why would I fetch every other property which is not of my interest, pure performance issue! I even had joins on multiple tables as

Code:

List<someTable> someTableList = session.createQuery("from someTable someTableAlias 
left join fetch someTableAlias.someParentA
left join fetch someTableAlias.someParentB
... and so on
)

As someTable has a mapping of many-to-one on these two parents, this one query was good enough to get me any field to be displayed. But there is a serious performance issue.

Not giving criteria a try and as I really like HQL, I tried the value injection via property methods or fields (does not need constructors) as

Code:

session.createQuery(
"select st.stNumber as stNumber, st.stDate as stDate "
+ " from SomeTable st "
+ " where st.someTableId < 1000")
.setResultTransformer( Transformers.aliasToBean(database.SomeTable.class))
.list();

This is working like a charm just like the way you suggested for createSQLQuery transformer, but what when I want to load some of its parents properties only, as lets say, SomeTable has a parent called SomedParent and I want to access one of the fields of this parent only

Code:

session.createQuery(
"select st.stNumber as stNumber, st.stDate as stDate, st.someParent.someParentField as someParentField "
+ " from SomeTable st "
+ " where st.someTableId < 1000")
.setResultTransformer( Transformers.aliasToBean(database.SomeTable.class))
.list();

The query portion st.someParent.someParentField as someParentField
should tell you what I am trying to do here.

I wonder if I am missing something here. I have not yet tried this in createSQLQuery transformer but if it works there it sure should work in HQL and criteria as well isn't it.

If there is none, I would want to request one!

Regards
Krishna