The next release of MetaWhere is shaping up to add a couple of cool new features to ActiveRecord – control over join type, and access to SQL functions supported by your database. Before I go into details about them, I’d like to ask those of you who are testing Rails 3-0-stable branch and ARel master already to try out the arel-2.0 branch, where these fun changes live. I’d appreciate feedback on how well they work for your apps. Now, on with the show.

Specifying Join Types

Let’s say our company is cutting costs, and we’ve heard that through some slip-up in HR we have managers with no employees. The axe must fall. But, fall on whom (besides the person in HR who made the slip-up)?

We might try writing something like this:

    Manager.joins(:employees).where(:employees => {:id => nil})
    => SELECT "managers".* FROM "managers"
       INNER JOIN "employees" ON "employees"."manager_id" = "managers"."id"
       WHERE ("employees"."id" IS NULL)

The problem is that this results in an inner join. An inner join is only going to include a row in the results if it has data on each side of the relationship. So, if a Manager has no Employees, he won’t show up in the list.

What we need is a left outer join, which only requires data in the left side of the relationship in order to be included in the results. If we switch our join to an includes, it’ll do the trick in this simple case:

    Manager.includes(:employees).where(:employees => {:id => nil})
    => SELECT "managers"."id" AS t0_r0, "managers"."name" AS t0_r1,
              "employees"."id" AS t1_r0, "employees"."manager_id" AS t1_r1,
              "employees"."name" AS t1_r2 FROM "managers"
       LEFT OUTER JOIN "employees" ON "employees"."article_id" = "managers"."id"
       WHERE ("employees"."id" IS NULL)

But aside from the query looking ugly, includes aren’t optimal in many cases, because they add the overhead of eager loading the included associations.

Enter MetaWhere:

    Manager.joins(:employees.outer).where(:employees => {:id => nil})
    => SELECT "managers".* FROM "managers"
       LEFT OUTER JOIN "employees" ON "employees"."manager_id" = "managers"."id"
       WHERE ("employees"."id" IS NULL)

By tacking on outer to the name of the association, we get exactly what we wanted!

Support for SQL Functions

That’s all well and good, but what if we want to find all managers with less than 3 employees? SQL supplies a COUNT function to count up the number of associated records when grouping. It’s what ActiveRecord uses when you call something like Manager.count.

With MetaWhere:

    Manager.joins(:employees.outer).group('managers.id').
            having(:employees => (:count[:id] < 3))
    => SELECT "managers".* FROM "managers"
       LEFT OUTER JOIN "employees" ON "employees"."manager_id" = "managers"."id"
       GROUP BY managers.id HAVING count("employees"."id") < 3

A few cool things are going on here.

First, :count[:id] created a call to the SQL COUNT() function. I enabled Symbol operators in this example. If you don’t do so, you can get the same results with having(:employees => {:count.func(:id).lt => 3}). (If you are used to using operators, you may have noticed that [], which used to be used for equality, is now used for functions. Equality has been moved to ».)

Second, since the parameter to count was supplied as a symbol, it was checked against the current relation first, to see if it’s the name of a column. Since it was, the column was referenced. If it had not been, it would have been treated as an SQL literal (that is, passed as-is with no quoting).

Multiple parameters are supported, as well. For instance, MySQL has a really nifty function called FIND_IN_SET. It takes two parameters, the value to find, and the set to find it in. Sets can also be specified as comma-separated values in a string. So, we can do stuff like:

    Manager.select('managers.*').
            select(:find_in_set[:id, '3,2,1'].as('position'))
    => SELECT managers.*, find_in_set("managers"."id",'3,2,1') AS position
       FROM "managers"

Oh, did I mention that MetaWhere::Functions work in select clauses and can be aliased with as? Yeah, that works, too.

Anyway, that’s about it. If you’ve got some time, please put the arel-2.0 branch through its paces, so that When Rails 3.0.2 hits, MetaWhere will be ready. Thanks in advance!