Skip to content
This repository

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

Adds subselects to the conditions of an ActiveRecord finder.

branch: master

Fetching latest commit…

Octocat-spinner-32-eaf2f5

Cannot retrieve the latest commit at this time

Octocat-spinner-32 lib
Octocat-spinner-32 tasks
Octocat-spinner-32 test
Octocat-spinner-32 .gitignore
Octocat-spinner-32 MIT-LICENSE
Octocat-spinner-32 README.rdoc
Octocat-spinner-32 Rakefile
Octocat-spinner-32 TODO
Octocat-spinner-32 init.rb
README.rdoc

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]]

Copyright © 2008 Los Angeles Times, released under the MIT license

Something went wrong with that request. Please try again.