ActiveRecord + HQL and an "IN" clause
Late last year as I was using MonoRail and ActiveRecord for a simple web application. I was helping my local church find volunteers with various skills to teach some basic computer courses to the the church staff. I wanted to keep track of the volunteers along with the skills they had. I used this as another opportunity to learn more about MonoRail and ActiveRecord.
The Database
The database couldn't be any simpler: I had a Trainer table, a Skill table and a join table to keep track of the 1:M relationship between a Trainer and their skills.
ActiveRecord
The ActiveRecord classes were equally easy to define (in fact, I created the ActiveRecord objects first and then used schema generation to generate the actual database tables).
The Problem Query
I used MonoRail to put together a couple of web pages for editing of the data. Then I created a "Report" page that allowed me to pick an arbritrary set of skills and get a list of all Trainers that had that particular skill.
In SQL, I'd use an "IN" clause like this:
select t.*
from Trainer t
inner join TrainerSkills ts on ts.TrainerId = t.id and ts.SkillId in (2,6)
In the query above, the IN clause of (2,6) contains the primary keys of the two Skill records selected by the user. Pretty simple SQL. I needed to figure out how to get this in HQL (Hibernate Query Language).
My first attempt was an almost exact port of the SQL syntax (HQL is very similar to SQL anyway):
public static Trainer[] FindBySkillset(Skill[] skills)
{ SimpleQuery<Trainer> q = new SimpleQuery<Trainer>("from Trainer t where t.Skills in (?)", skills); return q.Execute();
}
That didn't work. I got some cryptic error about having an "unindexed collection before []". So then I tried a variation of the above query where I used a named parameter and specifically indicated the parameter was a list:
public static Trainer[] FindBySkillset(Skill[] skills)
{ SimpleQuery<Trainer> q = new SimpleQuery<Trainer>("from Trainer t where t.Skills in (:skills)"); q.SetParameterList("skills", skills); return q.Execute();
}
That gave me another odd error. After digging around in the HQL docs as well as finding a forum post somewhere that showed a slightly different IN clause, I found out that I needed to "flip" the way I use the IN clause. In SQL, you'd say "WHERE xxx IN (values...)". In HQL, you give the list of values first and use the "elements" keyword to indicate which collection to match up those values to. The final working query:
public static Trainer[] FindBySkillset(Skill[] skills)
{ SimpleQuery<Trainer> q = new SimpleQuery<Trainer>("from Trainer t where ? in elements (t.Skills)", skills); return q.Execute();
}
Hopefully this helps out someone else.