record_filter¶ ↑

record_filter is a DSL for specifying ActiveRecord queries in pure Ruby. It has support for filters created on the fly and for named filters that are associated with object types. record_filter has the following top-level features:

• Pure ruby API eliminates the need for hard-coded SQL in most cases.

• Works seamlessly with existing ActiveRecord APIs, including named scopes.

• Supports creation of ad-hoc filters as well as named filters that can be associated with object types.

• Allows chaining of filters with each other and with named scopes to create complex queries.

• Takes advantage of the associations in your ActiveRecord objects for a clean implicit join API.

The complete RDoc documentation is available at aub.github.com/record_filter/rdoc/. This page is intended to be a getting started guide that should cover the most common uses.

gem install aub-record_filter --source=http://gems.github.com

In Rails, you’ll need to add this to your config/environment.rb file:

config.gem 'aub-record_filter', :lib => 'record_filter', :source => 'http://gems.github.com'

Given a Blog model having a has_many relationship with a Post model, a simple filter with conditions and joins might look like this.

Blog.filter do
  with(:created_at).greater_than(1.day.ago)
  having(:posts).with(:permalink, nil)
end

This could be expressed in ActiveRecord as:

Blog.all(
  :joins => :posts, 
  :conditions => {
    :posts => {:permalink => nil},
    :created_at => 1.day.ago..Time.now
  }
)

and it returns the same result, a list of Blog objects that are the result of the query. This type of filter is designed to be created on the fly, but if you have a filter that you would like to use in more than one place, it can be added to a class as a named filter. The following example creates the same filter as above and executes it:

class Blog < ActiveRecord::Base
  named_filter(:new_with_unlinked_posts) do
    with(:created_at).greater_than(1.day.ago)
    having(:posts).with(:permalink, nil)
  end
end

Blog.new_with_unlinked_posts

This returns the same result as the example above but with the advantages that it is easily reusable and that it can be combined with other named filters to produce a more complex query:

class Post < ActiveRecord::Base
  named_filter(:title_is_monkeys) { with(:title, 'monkeys') }
  named_filter(:permalink_is_donkeys) { with(:permalink, 'donkeys') }
end

Post.title_is_monkeys.permalink_is_donkeys

This example will return all of the posts that meet both animal-related conditions. There is no limit to the number of filters that can be combined, and because record_filter works seamlessly with named scopes, they can also be combined in this way as well.

Named filters can also be customized by taking any number of arguments. The example above can be replicated with the following filter:

class Post < ActiveRecord::Base
  named_filter(:with_title_and_permalink) do |title, permalink|
    with(:title, title)
    with(:permalink, permalink)
  end
end

Post.with_title_and_permalink('monkeys', 'donkeys')

Named filters can also be called from other named filters and will be invoked on the correct model even if called from a join.

class Comment < ActiveRecord::Base
  named_filter(:offensive) { with(:offensive, true) }
end

class Post < ActiveRecord::Base
  named_filter(:using_other_filter) do
    having(:comments) do
      offensive
    end
  end
end

Post.using_other_filter

record_filter supports all of the SQL query abstractions provided by ActiveRecord, specifically:

• Conditions

• Boolean operations

• Joins

• Limits

• Offsets

• Ordering

• Grouping

The following example shows the use of each of these techniques:

Post.filter do
  any_of do
    with(:permalink).is_null
    having(:comments) do
      with(:offensive, true)
    end
  end
  limit(100, 10)
  order(:created_at, :desc)
  group_by(:comments => :offensive)
end

Conditions are specified using the ‘with’ function, which takes as its first argument the name of the column to restrict. If a second argument is given, it will automatically be used as the value in an equality condition. The ‘with’ function will return a Restriction object that has methods to specify a number of different conditions and to negate them:

with(:permalink, 'aardvarks')            # ['permalink = ?', 'aardvarks']
with(:permalink).equal_to('sheep')       # ['permalink = ?', 'sheep']
with(:permalink).not.equal_to('cats')    # ['permailnk <> ?', 'cats']

with(:permalink, nil)                    # ['permalink IS NULL']
with(:permalink).is_null                 # ['permalink IS NULL']
with(:permalink, nil).not                # ['permalink IS NOT NULL']
with(:permalink).is_not_null             # ['permalink IS NOT NULL']

The following condition types are supported through the Restriction API:

• Equality

• Comparisons (> >= < <=)

• Between

• In

• Is null

• Like

• Negation of all of the above

And here are some examples. See the RDoc page for DSL::Restriction for more details on how to use them.

with(:featured_at).greater_than(Time.now)  # ['featured_at > ?', Time.now]

with(:price).lte(1000)                     # ['price <= ?', 1000]

with(:created_at).between(time_a..time_b)  # ['created_at BETWEEN ? AND ?', time_a, time_b]

with(:id).in([1, 2, 3])                    # ['id in (?)', [1, 2, 3]]

with(:id).not_in([4, 5, 6])                # ['id NOT IN (?)', [4, 5, 6]]

with(:content).like('%easy%')              # ['content LIKE ?', '%easy%']

with(:content).not.like('%hard%')          # ['content NOT LIKE ?', '%hard%']

The comparison operators (greater_than, less_than, greater_than_or_equal_to and less_than_or_equal_to) are aliased to their short forms (gt, lt, gte and lte).

It is also possible to specify multiple conditions on a single line using the ‘and’ and ‘or’ methods, eliminating the need to use a conjunction block in many common cases.

with(:expired_at).gt(Time.now).or.is_null  # ['expired_at > ? OR expired_at IS NULL', Time.now]

with(:id).gt(100).and.lt(1000)             # ['id > ? AND id < ?', 100, 1000]

Conditions can be combined with boolean operators using the methods all_of, any_of, none_of and not_all_of. These methods take a block where any conditions they contain will be combined using AND, OR and NOT to create the correct clause. The block can also contain any number of joins or other boolean operations. The default operator is all_of.

Post.filter do
  with(:id, 4)
  with(:permalink, 'ack')
end

# ['id = ? AND permalink = ?', 4, 'ack']

Post.filter do
  any_of do
    with(:id, 3)
    with(:permalink, 'booya')
  end
end

# ['id = ? OR permalink = ?', 3, 'booya']

Post.filter do
  none_of do
    with(:id, 2)
    with(:permalink, 'ouch')
  end
end

# ['NOT (id = ? OR permalink = ?)', 2, 'ouch']

Post.filter do
  not_all_of do
    with(:id, 1)
    with(:permalink, 'bonobo')
  end
end

# ['NOT (id = ? AND permalink = ?)', 1, 'bonobo']

Joins in record_filter come in two varieties. Using the information in ActiveRecord associations, it is possible to perform most joins easily using the ‘having’ method, which requires no specification of the columns to use for the join. In cases where an association does not apply, it is also possible to create an explicit join that can include both the columns to combine as well as restrictions on the columns in the join table.

In a filter for a Post model that has_many comments, the following two examples are equivalent:

having(:comments)

join(Comment, :join_type => :inner) do
  on(:id => :post_id)
end

With an explicit join, any number of columns can be matched in this way, and both join types accept a block in which any number of conditions, boolean operations, or other joins can be added. Explicit joins also allow conditions to be set on columns of the table being joined:

having(:comments).with(:offensive, true)

having(:comments) do
  with(:created_at).greater_than(2.days.ago)
end

join(Comment, :join_type => :inner) do
  on(:id => :commentable_id)
  on(:commentable_type).equal_to('Post')
  with(:created_at).less_than(1.year.ago)
end

With implicit joins, it is also possible to use a hash as the association name, in which case multiple joins can be created with one statement. If the comment model has_one Author, this example will join both tables and add a condition on the author.

having(:comments => :author).with(:name, 'Bob')

For both join types, an options hash can be provided as the second argument for passing the join type and/or an alias to use for the joined table. The join type defaults to :inner, and the alias defaults to a unique name for identifying the table. Using aliases allows you to join to a given table twice with two different names. How about a contrived example? Awesome.

Blog.filter do
  having(:posts, :join_type => :left, :alias => 'posts_1').with(:title, 'a')
  having(:posts, :alias => 'posts_2').with(:title, 'b')
end

# SELECT DISTINCT "blogs".* FROM "blogs"
#   LEFT OUTER JOIN "posts" AS posts_1 ON "blogs".id = posts_1.blog_id
#   INNER JOIN "posts" AS posts_2 ON "blogs".id = posts_2.blog_id
#   WHERE ((posts_1.title = 'a') AND (posts_2.title = 'b'))

These are specified using the ‘limit’ method, which takes two arguments, the limit and the offset. The offset is optional. For specifying only offsets, the ‘offset’ method is also available.

limit(100, 10)   # :limit => 100, :offset => 100
limit(100)       # :limit => 100
offset(10)       # :offset => 10

Ordering is done through the ‘order’ method, which accepts arguments for the column and direction. The column can either be passed as the name of a column in the class that is being filtered or as a hash that represents a path through the joined associations to the correct column. The direction argument should be either :asc or :desc and defaults to :asc if not given. Multiple calls to ‘order’ are allowed and will be applied in the order in which they were given.

Post.filter do
  having(:comments).with(:offensive, true)
  order(:created_at, :desc)
  order(:comments => :id)
end

# :order => "'posts'.created_at DESC posts__comments.id ASC"

Grouping is specified with the ‘group_by’ method, which accepts either the name of a column in the class that is being filtered or a hash that represents a path through the joined associations. If there are multiple calls to ‘group_by’ they will be combined in the final result, maintaining the order in which they were given.

Post.filter do
  having(:comments).with(:created_at).greater_than(1.hour.ago)
  group_by(:permalink)
  group_by(:comments => :offensive)
end

# :group => "'posts'.permalink, posts__comments.offensive'

Filters that include outer joins are automatically made distinct by record_filter. For filters that use inner joins, use the ‘distinct’ method in the DSL to force the select to be distinct.

Blog.filter do
  with(:created_at).greater_than(1.day.ago)
  having(:posts).with(:permalink, nil)
  distinct
end

# :select => "DISTINCT 'blogs'.*"

(The MIT License)

Copyright © 2008-2009 Aubrey Holland, Mat Brown

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the ‘Software’), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED ‘AS IS’, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.