RORY HANSEN

In an earlier blog post, I listed an example of using the find_by_sql function to return a collection of Artist objects. I have since changed my search method to use the find_all function, instead.

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

artist.rb (new code)
def self.find_by_first_letter(letter = "A")
   find_all ["ucase(left(artist_name, 1)) = ?", letter]
end

I think using the find_all function instead of the find_by_sql function follows the “Ruby on Rails way” more closely. Plus, it’s shorter and, perhaps, easier to understand for people who aren’t familiar with SQL.

By chance, I opened Firefox and visited my site, just to see how it looked. And I guess it’s a good thing I did, since I noticed a number of interesting anomalies.

• The text within my <code> block was quite small, so I’ve modified the CSS a little bit so that it should now be readable.
• Because all of my posts are created using WordPress, whenever I make double carriage returns in their text editor, the program automatically adds new <p> tags. This seems to totally screw with the <code> blocks, ending them prematurely. To bypass this problem, I’ll have to consciously avoid using two successive carriage returns and, instead, manually add a <br /> whenever I want a blank line.

I also noticed that I may have prevented people from commenting on my posts. So, if you had previously wanted to comment on something, but couldn’t, feel free to do so now!

Anyway, I’ll have to remember to view each of my posts with both Internet Explorer and Firefox from now on, just in case more of these anomalies appear.

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"])