sql_funk

sql_funk copied to clipboard

h1. Use case

Nothing better than a use case to explain the purpose of this gem.

• Suppose you have to build a dashboard in the admin area of your Rails app and you want to have the number of subscriptions per day.
• Suppose that you are using SQLite3 for development, MySQL for production (pretty standard setup)

Easily with SQL date functions, you could do that :

Subscriber.select('STRFTIME("%Y-%m-%d", created_at) AS day, COUNT(*) AS subscriptions').group('day')

Which would run this query :

SELECT STRFTIME("%Y-%m-%d", created_at) AS day, COUNT(*) AS subscriptions
FROM subscribers
GROUP BY day

... but wait... now the app has to go live in my production env which uses MySQL ! Replace @STRFTIME()@ with @DATE_FORMAT()@. What if tomorrow I switch to PostgreSQL ? Replace @DATE_FORMAT()@ with @DATE_TRUNC()@.

h1. SqlFunk to the rescue

The SqlFunk gem translates SQL functions that do the exact same thing, but have different syntax in each RDBMS that is being used.

So far, these functions are implemented :

• @count_by(column_name, :group_by => "day"|"month"|"year")@
• ... more to come

h1. Installation

In your Gemfile:

gem 'sql_funk'

h1. Example

Given that you have a Subscriber model.

  Subscriber.count_by("created_at", :group_by => "day")

Will generate these queries depending on the database adapter that is being used :

  # SQLite
  SELECT STRFTIME("%Y-%m-%d", created_at) AS day, COUNT(*) AS subscriptions
  FROM subscribers
  GROUP BY day
  
  # MySQL
  SELECT DATE_FORMAT(created_at, "%Y-%m") AS day, COUNT(*) AS subscriptions
  FROM subscribers
  GROUP BY day
  
  # PostgreSQL
  SELECT DATE_TRUNC("day", created_at) AS day, COUNT(*) AS subscriptions
  FROM subscribers
  GROUP BY day