Unixmonkey.net

For some complex searches, I find myself needing to build a query with some optional conditions that may or may not exist based on search terms.

I could concatenate an SQL string, but when we are dealing with user-supplied input, we need to parameterize the sql query with question marks (?) for binding parameters to to avoid SQL injection attacks.

But I may not know how many parameters I’m actually going to use in a query.

With a form like this:

Show me my friends: 
  living in: [   ]
  whose hobby is: () skating, () fishing, () basketball
  over: [  ] years old

  *(no required fields)

At first stab, you could try something like this:

User.friends.find(:all, :conditions => [
   'town LIKE ? AND hobby = ? AND age >= ?', 
   "%#{params[:town]}%", params[:hobby], params[:age] 
])

This would work, but only if all fields are filled out; otherwise the SQL generated would break looking like this if someone only filled in the name part of the form:

"SELECT * FROM users WHERE town LIKE "%indianapolis%" AND hobby = '' AND age >= ''"

There are some plugins like Ezra’s Ez-Where to handle query building; but how about we try using the built-in symbol key interpolation as described here in ActiveRecord to get the job done.

This lets you use named :symbols in place of question marks for binding.

Find lets you supply your conditions as a 2-element array with a string, and a corresponding hash like so:

User.friends.find(:all, :conditions => [ 
  'town LIKE :town AND hobby = :hobby AND age >= :age', 
  { :town => "%#{params[:town]}%" , :hobby => params[:hobby],  :age = params[:age] }
])

Again, this will work just fine if all fields are filled out; but how do we omit conditions and hash keys?

Our final SQL string can be built by joining conditions with AND (or OR if your app suggests it), and stuffing new key/value pairs into our arguments hash, or with Hash.merge!

First we’ll set up our search method like so:

def results
  conditions  = []
  arguments = {}
 
  unless params[:town].blank?
    conditions << 'town LIKE :town'
    arguments[:town] = "%#{params[:town]}%"
  end
 
  unless params[:hobby].blank?
    conditions << 'hobby = :hobby'
    arguments[:hobby] = params[:hobby]
  end
 
  unless params[:age].blank?
    conditions << 'age >= :age'
    arguments[:age] = params[:age]
  end
 
  all_conditions = conditions.join(' AND ')
 
  @user_friends = User.friends.find(:all, :conditions => [all_conditions, arguments])
end

An alternative is to use Hash.merge! like so:
(this will let you assign more than one key/value pair at a time or to combine hashes)

instead of:

arguments[:town]  = "%#{params[:town]}%"
arguments[:hobby] = params[:hobby]

  arguments.merge!({ 
    :town  => "%#{params[:town]}%",
    :hobby => params[:hobby]
  })

Maybe not as elegant as using a plugin, but certainly clear and flexible.

I’ve been playing around with Shoes (shoooes.net) lately as a way to put a cross-platform graphical user interface (GUI) on some of my small purpose-built command-line ruby scripts.

I find that it is quite easy to get started with, and lends a lot of flexibility to the way your program is structured and displayed. However, the structure feels a little bit alien compared to everyday ruby, and there are some gotcha’s you need to keep in mind while developing for Shoes.

I feel I must preface this article by saying that Shoes has excellent documentation, _why (the lucky stiff) turns documentation into its own art form. The manual, “Nobody Knows Shoes” reads a lot like a comic book, full of _why’s own original artwork and clippings from old-timey photos and art, and is complimented by the documentation at help.shoooes.net

I had a bit of trouble at first getting ActiveRecord to interface with a database from a straight port from one of my console apps because I glossed over the parts of the manual that detail the tricky behavior of the garbage collector reaping predefined classes after the app’s initial load.

The fix is pretty simple. Stick all your classes in an external file (or many) and load them using ‘require’.

Anyhow, here is a barebones example of a working implementation for using ActiveRecord in Shoes:

# in foo.rb
class Foo < ActiveRecord::Base
end
 
#in app.rb
Shoes.setup do
  gem 'activerecord'
  require 'active_record'
  ActiveRecord::Base.establish_connection(
    :adapter   => 'sqlite3',
    :dbfile    => 'foos_db.sqlite3'
  )
  require 'foo'
end
Shoes.app do
  @foos = Foo.find(:all)
  para @foos
end

Now, this example requires there is an existing sqlite database with a foos table, change out the establish_connection parameters to connect to any other database. The gem ‘activerecord’ statment tells shoes to install the activerecord gem into the shoes ruby library if it isn’t already there.

If you don’t already have a database, and just want to use a db to act as a storage layer for your app, then you might want to use ActiveRecord::Schema.define to create a database and setup the tables the same way you do for Rails migrations.

Here is a more complete example of an app to keep track of notes using ActiveRecord as the backend. I like the “base class that inherits from Shoes” pattern, so I’m using that here.

# in note.rb
class Note < ActiveRecord::Base
end
 
# in app.rb
Shoes.setup do
  gem 'activerecord' # install AR if not found
 
  require 'active_record'
  require 'fileutils'
 
  ActiveRecord::Base.establish_connection(
    :adapter   => 'sqlite3',
    :dbfile    => 'shoes_app.sqlite3'
  )
 
  # create the db if not found
  unless File.exist?("shoes_app.sqlite3")
    ActiveRecord::Schema.define do
      create_table :notes do |t|
        t.column :message, :string
      end
    end
  end
 
end
 
class ShoesApp < Shoes
  require 'note'
 
  url '/', :index
 
  def index
    para 'Say something...'
    flow do
      @note = edit_line
      button 'OK' do
        Note.new(:message => @note.text).save
        @note.text = ''
        @result.replace get_notes  
      end
    end
    @result = para get_notes
  end
 
  def get_notes
    messages = []
    notes = Note.find(:all, :select => 'message')
    notes.each do |foo|
      messages << foo.message
    end
    out = messages.join("n")
  end
 
end
 
Shoes.app :title => 'Notes', :width => 260, :height => 350

Here’s a screenshot:

There you are; a cross-platform desktop app that doesn’t require a full-on build environment, and can be distributed with the source exposed for later improvements.

The first time this runs, it installs Activerecord, requires it, establishes a connection, creates the table unless one already exists. Then it shows a form to add notes followed by all the existing notes in the database. Adding a new note refreshes the notes shown.

This isn’t exactly a polished app with full CRUD, but should prove a good introduction to Shoes for someone used to working with ActiveRecord.

I’ve got some data in an excel file that I need to put in the database and its far too much to do by hand, what will I do?

Lets throw some ruby at the problem!

First, excel it too darn complicated and proprietary a format to even mess with unless you are creating something really worth it, so lets open that .xls with Excel or OpenOffice and do a File -> Save As -> .csv (comma separated values) to get a file that is easier to work with.

Now, we could write our own CSV parser since its such a simple format, but why futz with it when someone else has already put out a good library for that will likely be more error tolerant? Lets use FasterCSV, as its pretty well-known.

Install by issuing:

sudo gem install fastercsv

Now you can just fire up script/console of your Rails app and type in the below, or just put this in a database migration to slurp up all that good spreadsheet data.

The below assumes you have a ‘users’ table with fields name, address, and email that are also rows in your excel file. Adjust as necessary.

require 'fastercsv'
FasterCSV.foreach("#{RAILS_ROOT}/myfile.csv") do |row|
  record = User.new(
    :name    => row[0], # first column of csv file
    :address => row[1], # second column
    :email   => row[2]  # third
  )
  record.save
end

That’s pretty awesome; now how can I export that stuff in the database back out to Excel again?

Lets use Ruport, the Ruby report gem!

sudo gem install acts_as_reportable

Toss the require statement somewhere obvious (like environment.rb or above the model you want to export), and put ‘acts_as_reportable’ in your model declaration.

require 'ruport'
class User < ActiveRecord::Base
  acts_as_reportable
end

Now I can do this kind of stuff to export to a csv file (again with script/console, but a migration should work equally well):

content = User.report_table.as(:csv) # convert your model table to CSV

or

content = User.report_table_by_sql("SELECT name, address, email FROM users").as(:csv)

Then write that to a file like so:

file = File.open("#{RAILS_ROOT}/report.csv", "w") # open file
file.print(content) # print that csv content to the open file
file.close          # close the file

Open that CSV file with Excel and amaze the sales team, your boss, or whoever.

This is a narrow view of what we can do with FasterCSV and Ruport, but I’m sure you can see how you could build out a format.csv in a respond_to block in a Rails controller, or have a setter in your model that sucks in an uploaded CSV to create some records.

These are some pretty great libraries, and I’m very glad they were able to help me load, combine, query and output some data I’d been working with in a pinch.

I hope this post serves to help someone else in a similar situation.