Subselector¶ ↑

Adds subselects to the conditions of an ActiveRecord finder.

Description¶ ↑

For a column in which you wish to perform a subselect, pass a hash indicating what kind of subselect with a key that must be one of the following: :in, :not_in, :equals, :not_equals.

Use :equals and :not_equals for subselects that return one row. Use :in and :not_in for more than one row.

The value of this key can be another hash that you would pass to the model’s finder, remember to set :select to constrain the results to one column. This hash can be as simple or complex as any options passed to find in ActiveRecord model. By default, the subselect runs off of the table the outer select runs, if you wish you can specify the table name with the :model key on which you wish to perform the subselect.

You can also pass a string if you wish to construct the subselect query yourself.

If you are using conditions in array form, specify the type of subselect in the string and pass just the subselect hash as the bind variable.

Examples¶ ↑

Here are the current examples for a set of critics:

c = Critic.find(:all)
c.map { |c| [c.login, c.active] }   #=> [["reid", true], ["dewey", true], ["doug", false]]

Subselect passed in as a string:

c = Critic.find(:all, :conditions => { :id => {:in => 'select id from critics where active = true' } })
#=> select * from critics where id in (select id from critics where active = true)
c.map { |c| [c.login, c.active] }   #=> [["reid", true], ["dewey", true]]

Subselect as a hash and conditions as a string:

c = Critic.find(:all, :conditions => { :id => {:in => {:select => :id, :conditions => 'active = true' } } })
#=> select * from critics where id in (select id from critics where active = true)
c.map { |c| [c.login, c.active] }   #=> [["reid", true], ["dewey", true]]

Subselect as a hash:

c = Critic.find(:all, :conditions => { :id => {:in => {:select => :id, :conditions => {:active => false} } } })
#=> select * from critics where id in (select id from critics where active = false)
c.map { |c| [c.login, c.active] }   #=> [["doug", false]]

Subselect negated:

c = Critic.find(:all, :conditions => { :id => {:not_in => {:select => :id, :conditions => {:active => true} } } })
#=> select * from critics where id not in (select id from critics where active = true)
c.map { |c| [c.login, c.active] }   #=> [["doug", false]]

Subselect on a different model:

c = Critic.find(:all, :conditions => { :id => {:in => 
    {:model => :rankings, :select => :critic_id, :conditions => {:week => 39} } } })
#=> select * from critics where id in (select critic_id from rankings where week = 39)
c.map { |c| [c.login, c.active] }   #=> [["reid", true,], ["doug", false]]

Subselect with array conditons:

c = Critic.find(:all, :conditions => ['id in (?)', {:select => :id, :conditions => 'active = true' }])  
#=> select * from critics where id in (select id from critics where active = true)
c.map { |c| [c.login, c.active] }   #=> [["reid", true], ["dewey", true]]

You can specify the model in this form too:

c = Critic.find(:all, :conditions => ['id in (?)', 
   {:model => :rankings, :select => :critic_id, :conditions => {:week => 39} }]) 
#=> select * from critics where id in (select critic_id from rankings where week = 39)
c.map { |c| [c.login, c.active] }   #=> [["reid", true,], ["doug", false]]

You can even use named bind variables:

c = Critic.find(:all, :conditions => ['id not in (:foo)', {:foo => {:select => :id, :conditions => {:active => false} } }])  
#=> select * from critics where id not in (select id from critics where active = false)
c.map { |c| [c.login, c.active] }   #=> [["reid", true], ["dewey", true]]

Calculations are also allowed:

c = Critic.find(:all, :conditions => { :id => {:in => {:select => :id, :calculation => :min} } })
#=> select * from critics where id in (select min(critics.id) from critics)
c.map { |c| [c.login, c.active] }   #=> [["reid", true]]

reidmix/subselector

Subselector¶ ↑

Description¶ ↑

Examples¶ ↑