Read my latest article: 8 things I look for in a Ruby on Rails app (posted Thu, 06 Jul 2017 16:59:00 GMT)

Using BETWEEN for SQL comparisons

Posted by Sat, 14 Nov 2009 19:55:00 GMT

Recently, Carlos, suggested that I should start sharing some basic SQL tips that help with performance and/or general usage. I recently came across some code that I didn’t like to read and/or write. For example, let’s take the following…


SELECT * FROM brochures WHERE published_at <= now() AND archived_at >= now()

Essentially, this is pulling back some data WHERE the the brochures are considered published. (We have a project that allows people to manage their brochure launch dates ahead of time.) In fact, in this project, we have no less than 6-8 dates in the database that we’re comparing data on and it’s easy to get lost in the logic when trying to understand it.

Now, there isn’t anything inheriently wrong with how this condition is constuctued. As a matter of personal taste, I find it annoying to mentally parse. Also, I find having to write now() more than once in a WHERE clause to feel like I’m repeating myself.

Read it outloud…

“WHERE the brochures published at date is less than and/or equal to right now AND the archived date is greater than and/or equal to now.”

Who talks like that?

Luckily, there is a better and in my opinion, a more readable way to express this is with the BETWEEN construct in SQL. (postgresql docs, mysql docs)


SELECT * FROM brochures WHERE now() BETWEEN published_at AND archived_at

Let’s read this outloud…

“WHERE the current date is between the published at and archived at dates.”

This sounds more natural to me.

Additionally, you can also do the inverse with NOT.


SELECT ... WHERE now() NOT BETWEEN brochures.published_at AND brochures.archive_at

Remember kids, “code is for humans first and computers second.”—Martin Fowler

Launching Ruby on Rails projects, the video

Posted by Wed, 11 Nov 2009 17:00:00 GMT

For those of you who didn’t make it to Rails Underground in July to witness my mind-blowing talk, Launching Ruby on Rails projects , it appears that Skills Matter has finally posted a video of it online. :-)

The sound levels are really low… but hopefully you’ll find it helpful.

You can also view the slides.

Related Posts

Flash Message Conductor now a Gem

Posted by Tue, 13 Oct 2009 14:30:00 GMT

We’ve been doing some early (or late… if you’re a half-full kind of person) spring cleaning on some of our projects. One of the small projects, flash_message_conductor, which we released last year as a plugin is now a gem. We’ve been moving away from using plugins in favor of gems as we like locking in specific released versions and being able to specify them in our environment.rb file is quite convenient.

To install, just run the following:


  sudo gem install flash-message-conductor --source=http://gemcutter.org
  Successfully installed flash-message-conductor-1.0.0
  1 gem installed
  Installing ri documentation for flash-message-conductor-1.0.0...
  Installing RDoc documentation for flash-message-conductor-1.0.0...

You’ll then just need to include the following in your config/environment.rb file.

Rails::Initializer.run do |config|
  # ...
  config.gem 'flash-message-conductor', :lib => 'flash_message_conductor', :source => "http://gemcutter.org"
end

You can take a peak at the README for usage examples.

We’ll be packaging up a handful of our various plugins that we reuse on projects and moving them to gems. Stay tuned… :-)

Planting the seeds

Posted by Sat, 05 Sep 2009 12:00:00 GMT

Yesterday, the Rails team released 2.3.4, which includes standardized way for loading seed data into your application so that you didn’t have to clutter your database migrations.

I noticed a few comments on some blogs where people were asking how to use this new feature, so here is a quick runthrough a few ways that you can use it.

Populating Seed Data Approaches

The db/seeds.rb file is your playground. We’ve been evolving our seed file on a new project and it’s been great at allowing us to populate a really large data. Here are a few approaches that we’ve taken to diversify our data so that when we’re working on UI, we can have some diversified content.

Basic example

Any code that add to db/seeds.rb is going to executed when you run rake db:seed. You can do something as simple as:

# db/seeds.rb

Article.create(:title => 'My article title', :body => 'Lorem ipsum dolor sit amet, consectetur adipisicing elit')

Just create database records like you would in your Rails application or in script/console. Simple enough, right? Let’s play with a few other approaches that we’ve begun to use.

Use the names of real people

We’re using the Octopi gem to connect to github, collect all the names of people that follow me there, and using their names to seed our development database.

@robby_on_github = Octopi::User.find('robbyrussell')

# add a bunch of semi-real users
@robby_on_github.followers.each do |follower|
  github_person = Octopi::User.find(follower)
  next if github_person.name.nil?

  # split their name in half... good enough (like the goonies)
  first_name = github_person.name.split(' ')[0]
  last_name = github_person.name.split(' ')[1]
  new_person = Person.create(:first_name => first_name, :last_name => last_name, :email => Faker::Internet.email,
                             :password => 'secret', :password_confirmation => 'secret',
                             :github_username => follower, :website_url => github_person.blog)
  # ...
end

We do this with a few sources (twitter, github, etc..) to pull in the names of real people. If you want to be part of my seed data, you might consider following me on Github. ;-)

Use Faker for Fake data

You may have noticed in the previous code sample, that I used Faker in that code. We are using this a bunch in our seed data file. With Faker, you can generate a ton of fake data really easy.

person.links.create(:title => Faker::Lorem.words(rand(7)+1).join(' ').capitalize,
                    :url => "http://#{Faker::Internet.domain_name}/",
                    :description => Faker::Lorem.sentences(rand(4)+1).join(' '))

We might toss something like that into a method so that we can do the following:

@people = Person.find(:all)

500.times do
  generate_link_for(@people.sort_by{rand}[0])
end

...and we’ll get 500 links added randomly across all of the people we added to our system. You can get fairly creative here.

For example, we might even wanted random amounts of comments added to our links.

def generate_link_for(person)
  link = person.links.create(:title => Faker::Lorem.words(rand(7)+1).join(' ').capitalize,
                             :url => "http://#{Faker::Internet.domain_name}/",
                             :description => Faker::Lorem.sentences(rand(4)+1).join(' '))

  # let's randomly add some comments...
  if link.valid?
    rand(5).times do
      link.comments.create(:person_id => @people.sort_by{rand}[0].id,
                           :body => Faker::Lorem.paragraph(rand(3)+1))
    end
  end
end

It’s not beautiful, but it gets the job done. It makes navigating around the application really easy so that we aren’t having to constantly input new data all the time. As mentioned, it really helps when we’re working on the UI.

Your ideas?

We’re trying a handful of various approaches to seed our database. If you have some fun ways of populating your development database with data, we’d love to hear about it.

Using model constants for project sanity

Posted by Tue, 23 Jun 2009 05:39:00 GMT

On one of our larger client projects (approx. 160 models and growing…) we have a specific model that we refer to quite a bit throughout our code. This model contains less than 10 records, but each of them sits on top of an insanely large and complex set of data. Each record refers to a each of their regions that our client does business in.

For example… we have, Australia, United Kingdom, Canada, United States, and so forth. Each of these regional divisions has their own company code, which are barely distinguishable from the next. They make sense to our client, but when we’re not interacting with those codes on a regular basis, we have to look constantly look them up again to make sure we’re dealing with the right record.

I wanted to share something that we did to make this easier for our team to work around these codes, which we should have thought of long ago.

Let’s take the following mode, Division. We only have about 10 records in our database, but have conditional code throughout the site that are dependent upon which divisions specific actions are being triggered within. Each division has various business logic that we have to maintain.

Prior to our change, we’d come across a lot of code like:

# For all divisions except Canada, invoices are sent via email
# In Canada, invoices are sent via XML to a 3rd-party service
def process_invoices_for(division)
  if division.code == 'XIUHR12'
    # trigger method to send invoices to 3rd party service
    # ...
  else
    # batch up invoices and send via email
    # ...
  end
end

An alternative that we’d also find ourselves using was.

if division.name == 'Canada'

Hell, I think I’ve even seen if division.id == 2 somewhere in the code before. To be fair to ourselves, we did inherit this project a few years ago. ;-)

Throughout the code base, you’ll find business rules like this. Our developers all agreed that this was far from friendly and/or efficient and worst of all, it was extremely error-prone. There have been a few incidents where we read the code wrong and/or got them confused with one another. We were lacking a convention that we could all begin to rely on and use.

So, we decided to implement the following change.

Model Constants

You might already use constants in your Ruby on Rails application. It’s not uncommon to add a few into config/environment.rb and call it a day, but you might also consider scoping them within your models. (makes it much easier for you to maintain them as well)

In our scenario, we decided to add the following constants to our division model.

class Division < ActiveRecord::Base
  AFRICA      = self.find_by_code('XYU238')
  ASIA        = self.find_by_code('XIUHR73')
  AUSTRALIA   = self.find_by_code('XIUHR152')
  CANADA      = self.find_by_code('XIUHR12')
  USA         = self.find_by_code('XIUHR389')
  # etc..
end

What this will do is load up ech of these constants with the corresponding object. It’s basically the equivallent of us doing:

if division == Division.find_by_code('XIUHR389')

But, with this approach, we can stop worrying about their codes and use the division names that we’re talking about with our clients. Our client usually approaches us with, “In Australia, we need to do X,Y,Z differently than we do in the other divisions due to new government regulations.”

if division == Division::CANADA
  # ...
end

case division
  when Division::AFRICA
    #
  when Division::AUSTRALIA
    # ...
end

We are finding this to be much easier to read and maintain. When we’re dealing with a lot of complex business logic in the application, little changes like this can make a big difference.

If you have any alternative solutions, we’d love to hear them. Until then, we’ve been quite pleased with this approach. Perhaps you’ll find some value in it as well.

Aliasing resources in Ruby on Rails

Posted by Tue, 23 Jun 2009 05:00:00 GMT

Earlier today, a friend working on a project asked me how we approached routes on our website. If you take a quick peak at our website, you’ll see that we have URLs like so:

When we launched our new site a few months ago, we were working off an existing code base. We have a model named, TeamMember and a corresponding controller. When we decided to come up with new conventions for our URL structure, we opted to ditch the normal Rails conventions and go our own route. What we weren’t sure about was how to alias resources in our routes nicely. After some digging around, we came across the :as option.

So, our route was:

  map.resources :team_members

Which provided us with:

  • /team_members
  • /team_members/robby-russell

We simply added :as => 'who-we-are' to our route:

  map.resources :team_members, :as => 'who-we-are'

...and we got exactly what we were looking for in our URLs.


* /who-we-are
* /who-we-are/gary-blessington

If you look at our site, you’ll notice that we did this in a few areas of our application so that we could define our own URL structure that was more friendly for visitors and search engines.

Anyhow, just a quick tip for those who want to change up their URLs with Ruby on Rails.

p.s., if you know where I can find this documented, let me know so that I can provide a URL in this post for others. :-)

Older posts: 1 2 3 4 ... 13