Lately, it feels like I've been wading through mud. As if the ActiveRecord library has been doing nothing but preventing me from getting my work done. In my dreams, I could call MyEntity.Find() with a list of ICriterion objects, and get back what I want. I was having problems with that, because I want to select several object, left joining to a notes table, and return only the most recent note (with max(NoteDate)). While I can think it, and write it in raw SQL, it has been tough doing this in an ActiveRecord query. Just to see if it is possible, I tried HQL, and finally resorted to raw SQL. I learned that "raw SQL" has several requirements imposed on it:
* The returned result set must include the complete field list of all entities that you want to return (as far as I can tell).
* NHibernate will replace {nt.*} with a complete field list if you include "nt" as an alias for typeof(MyNote).
* Do not list an alias unless you are returning that entity in your SELECT.
I ended up succeeding using a statement similar to this:
IQuery q = sess.CreateSQLQuery(sql, new string[] { "t1", "t2", "t3"}
new Type[] { typeof(ActHistory), typeof(ActStatus), typeof(ActNotes) } );
Now, this is 2/28/2007, and I am using the trunk version of NHibernate, and the trunk version of ActiveRecord with a few custom patches, so your milage might vary.
I am strongly considering creating a view and an ActiveRecord class marked as read-only that maps to the view and has a few helpers for creating the "real" objects when needed. I'll post the results soon.
* The returned result set must include the complete field list of all entities that you want to return (as far as I can tell).
* NHibernate will replace {nt.*} with a complete field list if you include "nt" as an alias for typeof(MyNote).
* Do not list an alias unless you are returning that entity in your SELECT.
I ended up succeeding using a statement similar to this:
IQuery q = sess.CreateSQLQuery(sql, new string[] { "t1", "t2", "t3"}
new Type[] { typeof(ActHistory), typeof(ActStatus), typeof(ActNotes) } );
Now, this is 2/28/2007, and I am using the trunk version of NHibernate, and the trunk version of ActiveRecord with a few custom patches, so your milage might vary.
I am strongly considering creating a view and an ActiveRecord class marked as read-only that maps to the view and has a few helpers for creating the "real" objects when needed. I'll post the results soon.
Comments