Welcome to Giant Robots Smashing Into Other Giant Robots — a weblog about development, business, design and technology — written by thoughtbot.

depends

Sometimes in an app you need to do something in SQL and not in your app’s language. At t-bot we’re strictly MySql. And occasionally, MySql specific functions/syntax creeps into an app.

First its: 
1
2

  has_many :published_posts,
    :conditions => 'published_on < now()'

From an app that allowed users to future date their posts.

Then its: 
1
2
3
4
5
6
7

    @post = Post.find :first,
    :conditions => ['year(created_on) = ? and ' +
                    'month(created_on) = ? and ' +
                    'day(created_on) = ? and ' +
                    'keyword = ?']
                    params[:year], params[:month],
                    params[:day], params[:keyword]]

From an app that needed ‘pretty’ urls e.g. ‘posts/2007/1/15/some-blog-post’

Here’s a beauty doing some timezone logic in SQL instead of Ruby. This was done for performance reasons. 
1
2
3
4
5
6
7
8
9
10

      Event.find :all,
        :select => 'events.*',
        :joins => 'inner join localities on localities.id = events.locality_id ' +
          'inner join timezones on timezones.id = localities.timezone_id',
        :conditions => ['locality_id = ? and ' +
                        'month(timestampadd(HOUR, timezones.utc_offset, start_date)) = ? and ' +
                        'year(timestampadd(HOUR, timezones.utc_offset, start_date)) = ?',
                        locality.id,
                        locality.timezone.now.month,
                        locality.timezone.now.year]

timestampadd. That one scared me a bit.

Something even crazier from an app that need to ‘weigh’ comments depending on when they were made. Also done in SQL for performance reasons. This is from an ActiveRecord::Base#find call. 
1
2

  :select => "revisions.*, SUM(IF(comments.created_on > DATE(NOW())-INTERVAL 1 DAY, 5, IF(comments.created_on > DATE(NOW())-INTERVAL 1 WEEK, 3, IF(comments.c\
reated_on > DATE(NOW())-INTERVAL 1 MONTH, 1, 0)))) as comment_score",

I always hesitate when I have to dip into MySql because I don’t want to develop a dependency on it. Why? Because years of using generic database independent interfaces and an insistance on the ability to change databases without changing a line of code. The fact is I’ve never had to change a database vendor on an app ever. And to me that argument gets weaker and weaker every day.

If the time comes that you need to switch to PostgreSQL then tell your client that you’re going to need some time to account for any dependencies your app has developed on its old database. However, I doubt this is ever going to happen.

So stop feeling guilty about using timestampadd().

About this entry

You're reading an entry on GIANT ROBOTS SMASHING INTO OTHER GIANT ROBOTS, the company weblog of thoughtbot, inc.

Author:
Jared Carroll
Published:
July 31st 08:21 AM
Updated:
September 30th 09:57 AM
Sections:
Development

 

thoughtbot is hiring

We are hiring web developers and web designers in both Boston and New York, NY.

What are we up to?

We built Shoulda, an eclectic set of additions to Test::Unit; Paperclip to manage uploaded files without hassle; Jester, a REST/ActiveResource client library written in Javascript, and Squirrel, an enhancement for ActiveRecord's find syntax; — amongst some other projects.


Chad (President) and Jon (CTO) co-authored a technical book titled Pro Active Record: Databases with Ruby and Rails, which explores the ins and outs of the ActiveRecord ruby library. You can buy it today at Amazon.com.

About thoughtbot, inc.

We are a small web application development consulting business, with offices in Boston, MA and New York, NY. If you're looking to find a team for your next web development project or your new web application — get in touch.