Brita Build Status

Brita, from Community

No longer the worst

Brita gives you the ability to filter your ActiveRecord queries based on what exists in the URL params. No more building long, complicated query strings in your controller based on filters the user maybe specified. Instead, just follow a simple naming convention and let Brita 'purify' your results. Get rid of the extra junk; focus on the good stuff.

How to Use

  1. In your filter form, do something like this, using brita_syntax_for to name your form inputs a way Brita will understand: ruby = form_tag(current_url, :method => "get", :multipart => true) do = text_field_tag brita_syntax_for(:student, :last_name) = text_field_tag brita_syntax_for(:student, :gpa, :minimum) = select_tag brita_syntax_for(:student, :university) = text_field_tag brita_syntax_for(:course, :number) = submit_tag "Apply"

  2. In your controller, call purify on the model you want to query, passing in the params and (optionally) a default order to the results: ruby @students = Student.purify(params, :default_order => "students.created_at DESC")

  3. There is no #3 (although continue reading for more customization). @students will now contain all students that match any number of the filters specified by the user (e.g. students with last name of 'Johnson', students with a minimum GPA of 3.0 that go to Harvard, students that go to Boston College and take course number 2124, etc.). If the user didn't specify any filter, it'll contain all students.

How's It Work?

Brita uses the names of the query parameters to figure out the right query to run. The brita_syntax_for helper creates a name for the parameter and the purify function will inspect the parameters, find matches to the syntax it understands, and run the query it determines. So say we wanted to get all students with a minimum GPA of 3.0 that go to Harvard University and take course 3746. The URL queried may look like this when using Brita: ruby /students?student.university=harvard&student.min_gpa=3.0&course.number=3746 Brita takes those params and will construct the necessary query with the purify call, which in this case, would probably look like this: ruby Student.where("students.university = ? AND students.gpa >= ? AND courses.number = ?", "harvard", 3.0, 3746).joins(:courses)

Installation

The standard: add brita to your Gemfile ruby gem 'brita'

Limiting the URL length

By default, the name of the query parameter brita generates is simply the name of the model via the underscore convention (i.e. 'student_type' translates to the model StudentType) and the name of the column, joined with a .. This could lead to very long URLs with a complicated set of filters specified, so brita provides a way to define abbreviations for your models and columns if you want to either shorten your URLs or alter the names of the parameters.

class Student < ActiveRecord::Base
    brita_table_abbr :s
    brita_column_abbr :ln => :last_name, :uni => :university, :g => :gpa
end

If these abbreviations are defined, brita_syntax_for will use these instead when creating the parameter name. So brita_syntax_for(:student, :university) would return s.uni, allowing for shorter and cleaner URLs like this: ruby /students?s.uni=harvard&s.min_g=3.0&cou.num=3746

Restricting filters to specific columns

Now, it's quite possible you don't want to blindly filter on whatever parameter may exist and map to a valid model and column in your app. There are 4 different ways to restrict filters to a specific subset of tables and columns. The methods are listed hierarchically, meaning the very last one listed (the :all flag) will ignore any filters listed through the previous 3 means.

attr_accessible

By default, brita will allow filtering by any column specified as attr_accessible in the referenced model. Why? Well, I felt like that was a decent starting point to declare a default scope of what columns could be filtered. If the attribute could be mass-assigned, I figured it wasn't a stretch to assume it could be mass-filtered.

acts_as_brita

To start fresh and declare an entirely new default scope for filtering, you can define a list of columns via the acts_as_brita class method. Declaring this will ignore the attr_accessible attributes in queries. ruby class Student < ActiveRecord::Base brita_table_abbr :s brita_column_abbr :nm => :name, :yr => :graduation_year attr_accessible :name, :graduation_year acts_as_brita :name, :graduation_year, :email end With this configuration, a student's name, graduation_year, and email can be filtered in any purify call if it's found in the URL.

:only_on flag

Using either attr_accessible or acts_as_brita is a good way to provide a default set of columns that can always be filtered on. However, it's still quite possible you'll want to have views limited to a very precise set of filters. For those cases, you can pass :only_on to purify with a hash of tables and columns that are valid filters. Only URL parameters that map to a table and column specified in :only_on will be filtered on. Any others will simply be ignored.

@students = Student.purify(params, :only_on => {:student => [:name], :college => [:state, :size]})

With this call, students can only be filtered by their name, their college's state, and/or their college's size. It doesn't matter what else may be configured via acts_as_brita. If a URL param does not match to one of those 3 possible table-column pairings, it'll be ignored.

:all flag

Again, attr_accessible or acts_as_brita are great default scopes. But say you just want to go for simplicity and/or really trust your users and want to allow them to filter by whatever their heart desires in particular situations (perfect for admin backends). Simply set the :all flag to true in the purify call. ruby @students = Student.purify(params, :all => true) Anything in the URL params that maps to a valid table and column pairing will be considered in the filter call.

Note: If none of the 4 methods are used, everything is free game.

Also, while querying for exact matches is straightforward, other queries require a special naming convention in the params.

Queries other than IS EQUAL

Minimum Values

Pass in the :minimum flag to brita_syntax_for. The below generates the necessary name to return students with a minimum GPA of the value specified

brita_syntax_for(:student, :gpa, :minimum)

Maximum Values

Just change :minimum to :maximum

IS NOT EQUAL queries

If you want to filter and ignore a specific value, use the :ignore option. ruby brita_syntax_for(:student, :university, :ignore) So if the user specifies Harvard here, brita will do students.university != ?, "Harvard".

NULL and NOT NULL queries

To filter based on nil values, change the query value client-side to either null or not_null. For example, say you just want to ensure only students with a university were returned when a filter was applied. You could do this in your form: ruby = hidden_field_tag brita_syntax_for(:student, :university), "not_null"

:includes over :joins

If you want to eager load the associated tables over simply joining on them, simply pass in a :includes => true flag. ruby Student.purify(params, :includes => true)

Sorting

Filtering isn't the only power Brita has. It also provides the ability to order results based on the same naming conventions used above. Simply utilize params[:sort] and params[:order].

Default Sort

It's likely the first time you fetch results to display to the user, you'll want to return them in a specific order. To do so, simply specify default_order in your purify call. ```ruby

Note: We're now using the actual (plural) table name here, as this is just straight up SQL.

Student.purify(params, :default_order => "students.created_at DESC") `` Ifparams[:sort]andparams[:order]` are not specified, then students will be returned from newest to oldest.

Dynamic Sorts

Dynamic sorts use the exact same naming conventions (abbreviations and all) as above. ruby /students?hs.name=brentwood&sort=s.nm&order=a This translates to: ruby Student.where("high_schools.name = ?", "brentwood").joins(:high_school).order("students.name ASC") params[:order] can be d, desc, or DESC for descending sorts. Everything else translates to ascending.

Don't forget to use brita_syntax_for when generating your sort links to help with naming the parameter correctly so brita will understand it. ruby %a{ :href => current_url(:sort => brita_syntax_for(:student, :created_at), :order => "d") } Date Created

Debugging

Brita provides a very helpful function that'll return the SQL it performs from the params given (useful when you don't think brita is doing her job).

Instead of calling Student.purify(params, :default_order => "students.created_at DESC"), call Student.get_purify_sql(params, :default_order => "students.created_at"). This returns a hash describing the SQL brita would perform:

{:query_string=>"students.name = ? AND students.grade IN (?) AND universities.enrollment >= ?",
 :query_values=>["jim", ["freshman", "senior"], 1000],
 :joins=>[:university],
 :order_by => "students.created_at DESC"}

Other Notes

  • All queries are joined with an AND. Unfortunately, no ability to OR anything at the moment.
  • JOINs happen automagically. If I can determine that the referenced table-column pair exists and is linked to the existing model in some way, it'll JOIN.
  • Array values are fine. If the value of a query is an array, I'll change the query to column_value IN (?) instead of column_value = ?
  • The code should never throw any errors if an unknown column is referenced (usually a normal SQL error). So, if for some reason, program.fake_column=nada is in the params and fake_column is...well, fake, I'll just ignore it since the column doesn't exist and fake_ isn't a special, reserved keyword.
  • You can always sort by created_at or updated_at. This is because, if you're like me, when an instance was created or updated is often a column you sort by. However, you'll probably never have those as attr_accessible, so instead of having to define acts_as_brita just to get sorts working based on Rails default timestamps, it's allowed by default.

Issues and Feedback

Feel free to create an issue on Github for any bugs or desired features.

MIT License. Copyright 2012 Jamie Davidson

To Do

  • Support LIKE, ILIKE, and wildcard matches
  • Allow including only certain tables instead of simply including ALL tables over joining them