Loed Loed - 4 months ago 12
Ruby Question

Order query by day / month / year in rails

Let me explain want I want to do, I've a table

Clicks
where all clicks for products are saved. And I want to make a graph of how many times something is clicked by customers.

This graph needs a day, month and year view.

What I need for the graph to work is a hash or an object.

My idea is that I'm getting something like:

data( 2012 => [ 01 => 100, 02 => 300, 03 => 50 etc.], 2011 => [ 01 => 100, 02 => 300, 03 => 50 etc.])


How can I retrieve all the data from a postgresql database in such an order? I would like to get the
created_at
field and extract the date from there. In a later process, all items from a particular month must be counted.

I'm struggling with this problem quite a bit, could someone push me in the right direction?

Answer

The snippet you posted is for MySQL - to do the same in PostgreSQL:

@clicks = Click.select("count(*) as c, date_part('year', created_at) as y, date_part('month', created_at) as m").
                group("y", "m").
                where({:user_id => current_user.id})

This will give you a hash in the format [year, month] => count. To get it in the format you wanted, you'll need to tweak the results a bit:

results = @clicks.inject({}) do |m, kv|
  year, month = kv.first
  clicks = kv.last
  m[year] ||= {}
  m[year][month] = clicks
  m
end

Or a bit more concise:

results = @clicks.each_with_object({}) do |((year, month), clicks), m|
  (m[year] ||= {})[month] = clicks
end