0

i have a method that works just fine (with rails 3 and PostgreSQL) ; i just wonder if it's possible to make it database-agnostic :

FarmGatePrice.average :price, :group => 'EXTRACT(WEEK FROM date)'

As i understand it, methods to extract a week ISO number from a date are always database-specific.

I could use something like a virtual attribute :week as seen here, but it seems the :group options only accepts raw SQL. I've also seen this solution but it doesn't fit my needs. Another way would be to use a database view, or even to add a :week column filled by a callback method - but i'd prefer not to mess with my schema.

So, any clue ?

Community
  • 1
  • 1
m_x
  • 12,357
  • 7
  • 46
  • 60

1 Answers1

1

Nope - if you need to do a group by week, you're better off saving it directly in the database as an extra column set via a before_save callback.

As you say, extracting a week from a date is non-trivial, and so it's best to let Rails handle this rather than your database. This will improve performance too.

Update:

Example callback:

def Thing

  before_save :set week

  private
     def set_week
        self.week = self.date.cweek
     end
end
Frankie Roberto
  • 1,349
  • 9
  • 9
  • Sorry, but i don't get it when you say it would improve performance. Aren't stored procedures supposed to be better ? ...and it does not answer the "how" part of the question. – m_x Jul 01 '11 at 07:35
  • 1
    I doubt that stored procedures would ever be as fast as an index on a simple integer column. Besides, stored procedures mean putting your business logic into your database - which isn't the Rails way (do some Googling to read the arguments around this). Have updated the answer with an example callback. – Frankie Roberto Jul 01 '11 at 11:53