Wednesday, February 28, 2007

Wading through mud with Castle ActiveRecord

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.

Labels: ,


Anonymous Ayende Rahien said...

Please post on the user list about it, there are better ways.

March 01, 2007 11:33 AM  

Post a Comment

Subscribe to Post Comments [Atom]

<< Home