Rails database specific queries for Heroku

Question

I've been deploying some apps to Heroku recently. I run MySQL on my local dev machine and have spent a little while updating some of my scopes to work in PostgreSQL. However one i have received an error on is proving difficult to change.

For the time being i've got a database specific case statement in my model. I understand why the error regarding the MySQL date functions is occurring, but im not sure if this is the most efficient solution. Does anyone have a better way of implementing a fix that will work with both MySQL and PostgreSQL?

  case ActiveRecord::Base.connection.adapter_name
  when 'PostgreSQL'
    named_scope :by_year, lambda { |*args| {:conditions => ["published = ? AND (date_part('year', created_at) = ?)", true, (args.first)], :order => "created_at DESC"} }
    named_scope :by_month, lambda { |*args| {:conditions => ["published = ? AND (date_part('month', created_at) = ?)", true, (args.first)], :order => "created_at DESC"} }
    named_scope :by_day, lambda { |*args| {:conditions => ["published = ? AND (date_part('day', created_at) = ?)", true, (args.first)], :order => "created_at DESC"} }
  else
    named_scope :by_year, lambda { |*args| {:conditions => ["published = ? AND (YEAR(created_at) = ?)", true, (args.first)], :order => "created_at DESC"} }
    named_scope :by_month, lambda { |*args| {:conditions => ["published = ? AND (MONTH(created_at) = ?)", true, (args.first)], :order => "created_at DESC"} }
    named_scope :by_day, lambda { |*args| {:conditions => ["published = ? AND (DAY(created_at) = ?)", true, (args.first)], :order => "created_at DESC"} }
  end

FYI, this is the PostgreSQL error that i am getting:

PGError: ERROR: function month(timestamp without time zone) does not exist LINE 1: ...T * FROM "articles" WHERE (((published = 't' AND (MONTH(crea... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. : SELECT * FROM "articles" WHERE (((published = 't' AND (MONTH(created_at) = '11')) AND (published = 't' AND (YEAR(created_at) = '2010'))) AND ("articles"."published" = 't')) ORDER BY created_at DESC LIMIT 5 OFFSET 0

Thanks in advance for any input anyone has.

mu is too short · Accepted Answer · 2011-04-05 22:22:26Z

4

You should be using the standard EXTRACT function:

named_scope :by_year, lambda { |*args| {:conditions => ["published = ? AND (extract(year from created_at) = ?)", true, (args.first)], :order => "created_at DESC"} }

Both PostgresSQL and MySQL support it.

answered Apr 5, 2011 at 22:22

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mike Lewis · Accepted Answer · 2011-04-05 19:10:58Z

2

Unfortunately this happens alot, however you have the general right idea.

Your first method of attack is to see if there is a function that exists both in MySQL and Postres, however this isn't possible in this case.

The one suggestion I would make is that there is a lot of code duplication in this solution. Considering the condition statement is the only compatible issue here, I would factor out the compatiablity check only for the condition:

Example (Semi-Psuedo Code):

named_scope :by_year, lambda { |*args| {:conditions => ["published = ? AND (#{by_year_condition} = ?)", true, (args.first)], :order => "created_at DESC"} }


#...code...

def by_year_condition
  if postgres
     "date_part('year', created_at)"
  else
     "YEAR(created_at)"
end

answered Apr 5, 2011 at 19:10

Mike Lewis

64.3k20 gold badges144 silver badges111 bronze badges

1 Comment

Pete Over a year ago

Yeah the code duplication is what really instigated my question, it was something i wanted to try and do without. You suggestion is certainly a good one, thanks.

anon · Accepted Answer · 2011-04-05 21:55:13Z

1

Another option would be to create computed columns for each of your date parts (day, month, and year) and to query directly against those. You could keep them up to date with your model code or with triggers. You'll also get the benefit of being able to index on various combinations on your year, month, and day columns. Databases are notoriously bad at correctly using indexes when you use a function in the where clause, especially when that function is pulling out a portion of data from the middle of the column.

The upside of having three separate columns is that your query will no longer rely on any vendor's implementations of SQL.

answered Apr 5, 2011 at 21:55

anon

Collectives™ on Stack Overflow

Rails database specific queries for Heroku

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related