CODEWITHSUNDEEP
sqlruby-on-railspostgresqlactiverecordruby-on-rails-4
MrPizzaFace
MrPizzaFace

Reputation: 8086

Optimized and efficient SQL query with Rails 4 ActiveRecord on postgreSQL database?

I'm not sure how to do this without hacking something completely ridiculous and experience has thought me that someone can probably suggest 1 or 2 lines of code that will do exactly what I want. I'm not sure where to look, so here's my question:

Say I want to display a chart of month to date daily signups. So I need an efficient sql statement.

I want this to return a hash like this:

{ "2014-08-01"=>83, "2014-08-02"=>67, "2014-08-03"=>109, ... }

Which I can then use to generate a wicked cool chart! =)

So I have no idea how to write this query. Can someone help me please? Thanks...

Here is my "hacked" attempt:

h = User.group("created_at").count
   (0.7ms)  SELECT COUNT(*) AS count_all, created_at AS created_at FROM "users"  GROUP BY created_at
 #=> {nil=>5, 2014-07-27 17:19:41 UTC=>1, 2014-07-19 00:23:54 UTC=>1, 2014-08-21 18:02:58 UTC=>1, 2014-08-21 18:39:31 UTC=>1, 2014-08-21 17:32:48 UTC=>1, 2014-08-21 17:21:46 UTC=>1, 2014-08-21 18:36:21 UTC=>1, 2014-08-21 16:29:37 UTC=>1, 2014-08-21 18:54:34 UTC=>1, 2014-08-21 17:56:24 UTC=>1, 2014-08-21 18:33:20 UTC=>1, 2014-08-21 16:27:17 UTC=>1, 2014-08-21 19:16:11 UTC=>1, 2014-08-21 18:41:14 UTC=>1, 2014-08-21 18:14:12 UTC=>1, 2014-08-21 18:30:46 UTC=>1, 2014-08-21 18:28:04 UTC=>1} 

dates = h.keys.compact.map {|j| j.to_s.match(/\d*-\d*-\d*/)[0] }

 #=> ["2014-07-27", "2014-07-19", "2014-08-21", "2014-08-21", "2014-08-21", "2014-08-21", "2014-08-21", "2014-08-21", "2014-08-21", "2014-08-21", "2014-08-21", "2014-08-21", "2014-08-21", "2014-08-21", "2014-08-21", "2014-08-21", "2014-08-21"] 

signUpHash = Hash.new 0
 #=> {} 
dates.map do |date|
signUpHash[date] += 1
end
 #=> [1, 1, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15] 
signUpHash
 #=> {"2014-07-27"=>1, "2014-07-19"=>1, "2014-08-21"=>15} 
signUpHash.sort.to_h
 #=> {"2014-07-19"=>1, "2014-07-27"=>1, "2014-08-21"=>15}

So though my way works (somewhat because it doesn't actually take into account month to date which would require more hacking) so... (I know there is a better, more efficient, more legible, more reliable, preferred, recommended, ideal ... (you get the point) ... way to do this. Please advise. Thanks! =)

Upvotes: 0

Views: 277

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324911

If writing SQL directly, you'd use date_trunc.

You haven't shown table definitions or sample data so I can't really test it or write a full query, but the general principle is:

SELECT date_trunc('month', created_at), count(created_at)
FROM mytable
GROUP BY date_trunc('month', created_at)
ORDER BY 1;

to produce a resultset of first-day-of-month to count pairs that're then trivially consumed and aggregated into a hash in the client. You could build the hash in the SQL query, but there's not really any benefit to doing so.

Note that an index on created_at cannot be used for this query. If you want to limit it to certain dates, do so with a WHERE clause on created_at, not using the resulting date_trunc'd values.

If you run this a lot and need it to be fast, and don't mind the additional write overhead of an extra index, you can create a composite index on the month:

create index blahwhateverindexname on mytablename( (date_trunc('month', created_at) );

Upvotes: 1

Related Questions