ActiveRecord is wonderful for the easy queries. But there are times, in the name of performance, when one must bust through the ORM facade and dip below into SQL.

Edit: Updated gist to fix SQL injection. Thanks to all the code reviewers, uh I mean commenters, for pointing it out!

Also pointed out in the comments, using lambdas in this pattern makes most sense for Rails 2. In Rails 3, AREL is composable so one can use class methods.

Wed Jun 29 13:55:27 EDT 2011

Consider this situation. You have users and projects. Users can belongs to
many projects throught the join table memberships. Our goal? All the users
who are NOT associated with a given project.

The schema: If you didn’t want to use SQL, you could get really nasty with something like this (written off the top of my head; I swear it took me all of 5 seconds!) Yikes! What did I just do there? On any decent sized project, my little rails process instantiated tons of ActiveRecord objects thereby eating up tons of memory. Given a large enough set of records, and small-ish application servers, its possible we could even start swapping to disk!! shudder Okay, okay, that’s the worst case. Assuming no disk swapping, garbage collection is still expensive! See here, here and here. Why instantiate objects only to ignore them? That’s wasteful! Let’s push that work down to the database level.

Here’s the scope to give us all users that are NOT a member of a particular project.

Let’s break it down:

Remember, we want a LEFT OUTER JOIN
because we want all the users whether they
have an an entry in the membership table or not.

The where clause filters out all users associated with the given project_id, leaving us all
users not associated with the project!!

Done! And done!

Got any nifty scope patterns you run across fairly often? Please share!