# I run a Rails site that needs to work across databases. My code is littered
# with conditionals to switch between PostgreSQL and SQLite. This is quite
# annoying. I was working on a query today that needs to extract year-month ->
# total price from the database. I had a nice SQL query that works in
# PostgreSQL but doesn't work in SQLite. Probably doesn't work in MySQL or MS
# SQL Server because, you know, the SQL standard is a completely fucking
# useless standard as it doesn't actually enable one to write a portable
# query.

# Here is the Ruby:

Job.find_all_by_supplier_id(supplier.id).
  group_by {|i| i.date_printed.to_s[0..6] }.
  map {|k, v| { :date   => k,
                :price  => v.inject(0) {|result, i| result + i.price }.to_f
               } unless k.nil? || k == "" }.
  compact.to_a.sort_by {|i| i[0] }

# Now, here's the SQL I am replacing:

%q{
  SELECT to_char(date_trunc('month', date_printed), 'YYYY-mm')
    as date, SUM(price) as price
  FROM jobs
  WHERE supplier_id = 4
  GROUP BY date
  ORDER BY date;
}

# The great thing about Ruby is how much more *readable* it is, amirite?
# And, gee, it's not like my database might optimize the SUM() call or the
# grouping or whatever and create indexes. Like a fucking RDBMS ought to.

# Sigh. It really would be lovely if SQL were to magically become a useful,
# portable *real* standard.

# Cue the .NET crowd with all their fancy LINQ. I'm jealous.

# Sigh again.