Skip to content

Searching a multiplexed database column

Multiplexing columns is an optimization used in some database  schema. The idea is to store multiple integers or boolean values  in a single integer column. The read and write operations are done using bit wise operators. For example we can store several flags in a single integer column, the first flag value is 1, the second 2, the third value is 4, etc.

Scoped search is able to expose multiplexed filed by specifying the offset and word size. In the common case of multiplexing booleans, the word size is 1.

scoped_search :on => :counters, :offset => 0 , :word_size => 6, :rename => :applied
scoped_search :on => :counters, :offset => 1 , :word_size => 6, :rename => :skipped

The search syntax will expose the multiplexed column as multiple columns.


Using scoped search UI helpers

Scoped search comes packed with some handy GUI helpers. An ajax auto completer that handles the auto-completer drop-down box, and column sort helpers.

The ajax auto-completer drop-down comes in two flavors one for JQuery and the other for Prototype. The code samples in this post will use the JQuery flavor.

Here is an example of the auto-completer ajax drop-down usage, in the view place the following form tag where you want the search box to show:

<%= form_tag books_path, :method => :get do %>
  <%= auto_complete_field_tag_jquery(:search, params[:search], {:placeholder => "Filter ..."}) %>
  <button id='submit_search'">Search</button>
<% end -%>

To control the look of the search box use the following css classes: ui-autocomplete-input, ui-autocomplete-loading and ui-autocomplete-error. The auto_complete_field_tag_jquery accepts additional options hash for further controlling it’s behavior such as delay and min_length.

Another useful helper method is the sort helper. The sort helper draws an arrow at the top of the sorted column pointing to the sort direction, it also adds order parameter to the url. Together with the ability of the scoped_search to add an order-by sql clause, it makes a complete sorting solution. The reason for sending a new request to the database instead of sorting in the client is that many applications uses pagination. When using pagination client side sort may cause wrong results. Here is an example of using the sort helper:

    <th><%= sort :name %></th>
    <th><%= sort :"author.last", :as => "Author" %></th>
  <% for book in @books %>
      <td><%= %></td>
      <td><%= %></td>
      <td><%= link_to "Show", book %></td>
      <td><%= link_to "Edit", edit_book_path(book) %></td>
      <td><%= link_to "Destroy", book, :confirm => 'Are you sure?', :method => :delete %></td>
  <% end %>

Applying permission model to search results

Enterprise applications usually have a role base access control (RBAC). The search results as well as the auto-completer suggestions must conform to the access control model. The recommended way to control the search results, using scoped_search, is chaining scopes. The scoped_search is a named scope (scope in rails 3), this means that it can be chained with other scopes in a natural way.

For example:

 scope :my_hosts, lambda {
where("owner_id = ?",

In the above code :my_hosts is the RBAC scope (defined in lines 1 to 3). In line 4 the search is chained to the RBAC scope.

For the auto-completer there is a new way of filtering the results in a similar way. In the Model we can define a scope called :compelter_scope , this scope should accept an options hash. The options hash is a way to pass parameters from the controller, such as the current user.

For example:
Model code:

scope :completer_scope, lambda { |options|
  where('owner_id = ?', options[:owner_id])

Controller code:

def auto_complete_search
  @items = Host.complete_for(params[:search], {:owner_id =>})
  render :json => @items

To read more about named scopes, here is a link to a detailed post on the subject: Skinny on scopes

Search with Sinatra now on github

The code for my mini_search, a simple RESTful API based search for a none-rails application, is now on github see: mini_search code.

Another hosted application that uses scoped_search

Written by Ohad Levy, the maker of Foreman:

Searching a key-value schema

Sometimes when designing a database schema the designer cannot determin the exact content that will be stored in the databse in advanced. The common solution for that is designing a Key-Value pairs schema (Sometimes refered as Attribute-Value schema).

There are a few flavors to the key-value schema I this post we will look at a simple one with one table that repeats the key name for every occurence of the key, and a more complicated one that uses a seperate key table.

Simple schema:


Advanced schema

1 1 1
1 2 2010
2 1 0
2 2 2010
3 1 1
3 2 2008

In scoped search there is a special declaration for a key-value db schema. The auto-completer complete both keys and values.

The scoped search declaration for the simple schema should look as follows:

scoped_search :in => :user_attributes, :on => :value,  :on_key=> :key_name, rename => :attr

For the second schema the search should be defined as follows:

scoped_search :in => :user_attributes, :on => :value, :in_key=> :attributes_name, :on_key=> :name

The auto completer will suggest the same syntax for both schema.

Adding Search to Sinatra

I was asked to add a search to an application that is written in java.
Initially I thought of implementing some of the search in java over hibernate, then I came up with another option.
All I wanted was a service that will expose the scoped search syntax and return the results as json. I tried running it as a Sinatra application, it turned out to be the following  short piece of code:

require 'sinatra'
require 'sinatra/activerecord'
require 'scoped_search'
require 'json'

set :database, 'postgresql://candlepin:candlepin@localhost/candlepin'
class Subscription < ActiveRecord::Base
  set_table_name 'cp_pool'
  belongs_to :organization, :foreign_key => :owner_id
  scoped_search :on => :productname, :complete_value => true, :rename => :product
  scoped_search :on => :activesubscription, :complete_value => {:true => true, :false => false}, :rename => :active
  scoped_search :on => :quantity, :complete_value => true
  scoped_search :on => :contractnumber, :complete_value => true, :rename => :contract
  scoped_search :on => :enddate, :complete_value => true, :rename => :expire
  scoped_search :on => :startdate, :complete_value => true, :rename => :begin
  scoped_search :in => :organization, :on => :displayname, :complete_value => true, :rename => :org

class Organization < ActiveRecord::Base
  set_table_name 'cp_owner'
  has_many :subscription

get '/subscriptions' do
  res = Subscription.search_for("#{params[:search]}")
  content_type :json

get '/subscriptions/auto_complete_search' do
  res = Subscription.complete_for("#{params[:search]}")
  content_type :json

That’s it. That is all the code I needed, in order to add search and auto-completer capabilities to a non-rails application.
Can it get any simpler then that?