SmartTuple: A Simple Yet Smart SQL Conditions Builder

Introduction

Sometimes we need to build SQL WHERE statements which are compound or conditional by nature. SmartTuple simplifies this task by letting us build statements of virtually unlimited complexity out of smaller ones.

SmartTuple is suitable for use with Ruby on Rails (ActiveRecord) and other Ruby frameworks and ORMs.

Setup (Rails 3)

In your app's Gemfile, add:

gem "smart_tuple"

To install the gem with RDoc/ri documentation, do a:

$ gem install smart_tuple

Otherwise, do a bundle install.

Setup (Rails 2)

In your app's config/environment.rb do a:

config.gem "smart_tuple"

To install the gem, do a:

$ gem sources --add http://rubygems.org
$ gem install smart_tuple

, or use rake gems:install.

Kickstart Demo

tup = SmartTuple.new(" AND ")
tup << {:brand => params[:brand]} if params[:brand].present?
tup << ["min_price >= ?", params[:min_price]] if params[:min_price].present?
tup << ["max_price <= ?", params[:max_price]] if params[:max_price].present?

@phones = Phone.find(:all, :conditions => tup.compile)

There's a number of ways you can use SmartTuple. Some of them is covered in the tutorial below.

Tutorial

Suppose we've got a mobile phone catalog with a search form. We are starting with a price filter of two values: min_price and max_price, both optional.

Filter logic:

• If the user hasn't input anything, the filter has no conditions (allows any record).
• If the user has input min_price, it's used in filter condition.
• If the user has input max_price, it's used in filter condition.
• If the user has input min_price and max_price, they both are used in filter condition.

Suppose th