record_filter¶ ↑

record_filter is a DSL for specifying criteria for 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.

gem install outoftime-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.find(
  :all, 
  :joins => :posts, 
  :conditions => ['posts.permalink IS NULL AND blogs.created_at > ?', 'blog-post', 1.day.ago)

and it returns the same result, a list of Blog objects that are returned from 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 Post < ActiveRecord::Base
  named_filter(:new_with_nil_permalink) do
    with(:created_at).greater_than(1.day.ago)
    having(:posts).with(:permalink, nil)
  end
end

Post.new_with_nil_permalink

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(:title, '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(:recursive_test) do
    having(:comments) do
      offensive
    end
  end
end

record_filter supports all of 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(10, 100)
  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')            # :conditions => ['permalink = ?', 'aardvarks']
with(:permalink).equal_to('sheep')       # :conditions => ['permalink = ?', 'sheep']
with(:permalink).not.equal_to('cats')    # :conditions => ['permailnk <> ?', 'cats']

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

The following condition types are supported through the Restriction API:

• Equality

• Comparisons (> >= < <=)

• Between

• In

• Is null

• Like

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 condition. 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

:conditions => ['id = ? AND permalink = ?', 4, 'ack']

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

:conditions => ['id = ? OR permalink = ?', 3, 'booya']

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

:conditions => ['NOT (id = ? OR permalink = ?', 2, 'ouch']

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, :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, :inner) do
  on(:id => :commentable_id)
  on(:commentable_type).equal_to('Post')
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')

These are specified using the ‘limit’ method, which takes two arguments, the offset and the limit. If only one argument is given, it is assumed to be the limit.

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

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'

(The MIT License)

Copyright © 2008 Mat Brown, Aubrey Holland

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.