jump to navigation

Subselector, Moneypenny November 1, 2008

Posted by reidmix in ActiveRecord, Code, Database, Example, Monkey Patch, Plugins & Gems, Rails.
Tags: , , , , ,
trackback

Building on Josh and Damon‘s idea of Hacking a Subselect in ActiveRecord, I wondered if you could bake this kind of functionality into ActiveRecord.  So Doug and I went digging into the rails code, and came up with a plugin that adds subselects to ActiveRecord which we call Subselector.

So far, it only works on the Hash version of conditions.

On a column you wish to perform a subselect, pass a hash with :in, :not_in, :equals, or :not_equals as the only key.  The value is any of the options you normally would pass to ActiveRecord find.  Notice that we make sure to select a single column with the :select option:

Critic.find(:all, :conditions => { :id => {:in => {:select => :id, :conditions => {:active => true} } } })

Although the example may be contrived, here, we are looking for a critics that are in a set of active critics. The SQL:

select * from critics where id in (select id from critics where active = false)

You can see by default it runs the subselect on the table of outer select.  It gets more interesting you want to run a query on another ActiveRecord model:

Critic.find(:all, :conditions => { :id => {:in => {:model => :rankings, :select => :critic_id, :conditions =>
  {:week => 39} } } })

Here we set :model to :rankings.  Rankings is the ActiveRecord model to perform the find, notice we select the :critic_id column, the SQL is:

select * from critics where id in (select critic_id from rankings where week = 39)

And of course you can always just pass a string as a value to the subselect:

Critic.find(:all, :conditions => { :id => {:not_in => 'select id from critics where active = true' } })

Here’s how subselector can be used with the original example:

Post.find(:all, :conditions => :id => {:in => { :select => :post_id, :conditions => {:blog_id => self.id}, :order => "published_at DESC", :limit => options[:limit] || 10, :offset => options[:offset])} }, :order => "published_at DESC")

UPDATE: Subselector now likes Condition Arrays and Named Bind Variables.

Just pass the hash as a bind variable and specify the type (in/equals) of subselect in the string, make sure to enclose your ‘?’ inside parentheses:

Critic.find(:all, :conditions => ['id in (?)', {:select => :id, :conditions => 'active = true' }])
Critic.find(:all, :conditions => ['id not in (?)', {:select => :id, :conditions => {:active => false} }])
Critic.find(:all, :conditions => ['id in (?)', {:model => :rankings, :select => :critic_id, :conditions => {:week => 39} }])

As you can see, you can format the subselect hash just as above and can specify another model to run the subselect on. If you prefer to use named bind variable hashes, they still work (yay) as you would expect. And you can assign the subselect using them:

Critic.find(:all, :conditions => ['id in (:subselect)', {:subselect => {:select => :id, :conditions => {:active => false} } }])

UPDATE 2: Now with no ActiveRecord breakage

We’ve run the rails ActiveRecord tests without any problems. Let me know if you find any problems.

About these ads

Comments»

1. noob - November 25, 2008

noob question: how do I install this, I’m using AR + Merb, but not rails?

2. reidmix - December 2, 2008

The way it works in rails is that rails will run the init.rb file after the frameworks are loaded (most notably, AR).

So, I’m not sure how Merb puts things together or if there is a built in way to load plugins, but if you get it to run the init.rb file after AR runs, it should patch it and be up and running.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: