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.

image

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).

image

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.

Technorati tags: , ,

10 Comments

  • You can do this query using ActiveRecord with nHibernate's Expressions. The method would look like something below. With ActiveRecord there are typically only a few instances where you need to use HQL.

    public static Trainer[] FindBySkillset(string skillIDList)
    {
    DetachedCriteria query = DetachedCriteria.For(typeof (Trainer), "Trainer")
    .CreateCriteria("Skills")
    .Add(Expression.In("Id", skillIDList.Split(','));
    return Trainer.FindAll(query);
    }

  • But doesn't this require that I iterate through my Skill[] and pull out the ID for skillIDList?

    And as someone who's done a lot of SQL in the past, I actually find it easier to write HQL queries vs. nHibernate Expressions. Maybe it's just me...

  • The SetParameterList method would work with an ICollection

  • Jason - what would the HQL for that look like?

  • public static Trainer[] FindBySkillset(int[] skills)

    {

    SimpleQuery q = new SimpleQuery("from Trainer t where t.Skills in (:skills)");

    q.SetParameterList("skills", skills);

    return q.Execute();

    }

    Pretty much the same as your earlier example but using an int array. I didn't read your earlier comment about iterating through Skill[] :)

  • Sorry....mistake above.... you'd need to change it to query Skills.Id

    public static Trainer[] FindBySkillset(int[] skills)

    {

    SimpleQuery q = new SimpleQuery("from Trainer t where t.Skills.Id in (:skills)");

    q.SetParameterList("skills", skills);

    return q.Execute();

    }

    I think your way is better :)

  • Jason -- Both queries looked promising. The first one (as you pointed out) did have a problem that resulted in "unindexed collection before []".

    I suspected NHibernate would have a problem with the second variation since "Skills" references a collection, not an object with an Id property. When I tried it, I got the error: "illegal syntax near collection: Id".

    Thanks for the comments!

  • I assumed with my example that you were selecting the skills to pull from a UI, so you would already have the list of Skills.ID that you needed.


    You can also do the same thing Jason did using the Expression.In method instead of passing in a string and spliting it.

    I agree that at first it does feel like it is easier to write the HQL. Once I got used to the nHibernate Expressions, I find it much quicker to write my queries.

    Also, if you are just going to write HQL, I would question the need to use ActiveRecord at all. ActiveRecord has a lot of built-in functions for CRUD operations that don't require you to write the HQL statements but instead uses nHibernate Expressions.

  • Justin,

    I already had a Skill[] that was automatically populated by MonoRail. The UI presented a list of skills and each one had a checkbox next to it. That form posted to a method with the following signature:

    public void ViewBySkill([ARDataBind("skills", AutoLoadBehavior.Always)] Skill[] skills)

    So I had my Skill[] and was ready to go. That's one of the things I love about MonoRail -- the smart data binding.

  • This post was VERY helpful, thanks for taking the time to post this. Good job!

Comments have been disabled for this content.