Rory Hansen

Note: Read this post to learn how to implement the same method using the find_all function. - Rory, July 3, 2005

In the lyrics website I’m developing, I’d like to be able to use URLs like this in my administrative back-end:

http://www.website.com/admin/artists/list/A

where, when I visit this URL, a list of artists who’s name begins with the letter A is displayed. This requires the use of the Ruby on Rails find_by_sql method, as there is no way of querying for artists in this manner using the built-in Active Record.

The first thing I did was add a new method to my Artist model that will allow me to search for artists by the first letter of their name.

artist.rb
class Artist < ActiveRecord::Base
   has_many :songs, :order => "song_title", :dependent => true
   has_many :albums, :order => "album_name", :dependent => true

def self.find_by_first_letter(letter = "A")
      find_by_sql ["select * from artists where ucase(left(artist_name, 1)) = ?", letter]
   end
end


As you can see, the method find_by_first_letter takes in an argument called “letter” and uses this argument in the function call to find_by_sql. You’ll notice that a question mark appears in the SQL statement where we would actually like the value associated with “letter” to go. Queries created this way are called Parameterized SQL Queries, and by using them, we avoid the possibility of having someone tamper with our query using SQL injection.

Then, in our action, all we have to do is call our new method.

admin_controller.rb
@artists = Artist.find_by_first_letter(@params["letter"])