QuirkeyCode

Restful Query

What

Restful Query is a module that when included in ActiveRecord or Sequel (as of 0.3.0) supplies an easy and safe way to query a database through a RESTful resource.

Why

The general RESTful resource generated by Rails and widely used across the community supplies only basic CRUD (Create Read Update Delete) functionality. The index action usually provides a list (probably paginated) of all the records for a given resource or within a given scope. Restful Query provides an easy way to create a RESTful API for querying records for a given resource.

For example, given a resource of Book, say you want to get a list of all Books whose created or added to the system in the last two weeks. The common solution would be to create another action on the resource for querying the books by date. With Restful Query its as easy as:

/books?query[created_at][gt]=2 weeks ago

Restful Query allows you to query the books by any and as many parameters you want and want to allow.

Usage

RestfulQuery provides extensions for both ActiveRecord and Sequel. The only difference is how to enable the extension.

With ActiveRecord/Rails

Including Restful Query in your Rails project is easy. Install it as a plugin or a gem in your app.

In the ActiveRecord model you want to query, invoke the can_query macro:

class Book < ActiveRecord::Base

  can_query

end

This will add a few methods to your model, notably the named_scope restful_query

With Sequel

RestfulQuery includes an extension for Sequel. Once the gem is installed:

require 'sequel/extensions/restful_query'

This extends the Sequel::Dataset class, allowing you to filter/order a dataset using the restful_query method.

General Usage

restful_query takes a hash with a special format and converts it into a conditions array. The format is intended to be easy to interpret and read within the URL as well as be easy to pass as Rails-like form params.

The ruby hash should look something like:

params[:query] = {'column' => {'operator' => 'value', ...}, ...}

Which looks like this before being coverted by ActionController’s params parser:

?query[column][operator]=value&...

A query can be made up of as many operators on as many columns as you like.

A column is a literal column in the database. You can query across tables by passing the :include option to the can_query macro. You can also exclude columns you dont want queried (password, private info) by passing :exclude_columns to can_query with an array of column names.

An operator is one of the following:

Multiple operators can be passed to for a single column like:

{'created_at' => {'gt' => '1 day ago', 'lt' => '1 hour ago'}}

There’s also a shorcut to ‘eq’ by eliminating the operator:

{'name' => 'Programming Ruby'}

is equivilent to:

{'name' => {'eq' => 'Programming Ruby'}}

A value is a string passed evaluated in the ActiveRecord conditions array. In the case of Date and Time like objects, Restful Query can make use of the chronic gem to parse string representations of time like those written about above. This solves for a lot of the inconsitancis between different languages/classes/apps representations of time as a string. If you pass :chronic => true to can_query the parser will automaticaly interpret the created_at and updated_at columns through chronic. If there are differnt columns you wish to interpret through chronic pass them in an array to :chronic in can_query. e.g:

class Book < ActiveRecord::Base

  can_query :chronic => ['last_viewed_at', :updated_at]

end

For ‘special values’ you can also pass a symbol-like string to ensure the value is converted to a literal.

Restful Query also provides the ability to sort/order results by passing a _sort key to the restful_query named_scope. _sort takes an array of strings with a simple but specific format of:

column-direction

Where direction is one of:

• asc
• desc
• up
• down

For example:

{'created_at' => {'gt' => '1 day ago'}, '_sort' => 'created_at-desc'}

or with multiple:

{'created_at' => {'gt' => '1 day ago'}, '_sort' => ['created_at-desc', 'name-asc']}

You can aslo pass a :default_sort option to can_query using the format above.

Implementing Restful Query in a resource index super simple. First, include the macro in the model, then in the controller:

class BooksController < ApplicationController

  def index
    @books = Book.restful_query(params[:query]).paginate(:page => (params[:page] || 1), :per_page => 20) 
    respond_to do |format|
      format.html 
      format.xml { render :xml => @books}
    end
  end
  
  ...
end

Since restful_query is just a named_scope it can be chained with other find/scope operations as with will_paginate’s paginate above.

Restful Query is also used to great effect in another gem o’ mine: qadmin

Dependencies

• rubygems >= 1.3.1
• chronic >= 0.2.3
• activesupport >= 2.2.0

Installing

sudo gem install restful_query
        

Or directly from github:

sudo gem install quirkey-restful_query -s http://gems.github.com
        

You can also install it as a rails plugin:

./script/plugin install git://github.com/quirkey/restful_query.git
        

Github in thier infinite awesomeness also lets you download the source in multiple formats.

Bugs/Feature Requests

The full and most up to date source is at github:

http://github.com/quirkey/restful_query/

You can clone or fork from there to fix or break the code.

I’m happy to take feature requests/bug reports. Just email me (contact below) or message me on github.

License

This code is free to use under the terms of the MIT license.

Contact

Feel free to email me at aaron at quirkey dot com.

Please check out my blog.

If you like or use this library – I don’t want donations – but you can recommend me on workingwithrails.com or hire me to work on your next project.