1

Here is my query: 

User.where("created_at >= ? AND created_at <=?", date1,date2).order('DATE(created_at) DESC').group("DATE(created_at)").count

and I get output as: 

{Thu, 15 May 2014=>1}

But I want to get output as 0 for the rest of the days. For ex 

{Thu, 15 May 2014=>1,Fri, 15 May 2014=>0}

What I want to get is Users created in a date range, ordered and grouped by created_at and number of such Users for each day. When no users are there for a particular day it should return 0, which the current query doesn't return.

Improve this question

4 Answers 4

Reset to default
3

I agree with Himesh

Try this: 

User.where("created_at >= ? AND created_at <=?", date1,date2).order('DATE(created_at) DESC').group("DATE(created_at)").count

Which will give: 

{Thu, 15 May 2014=>1}

Get the date range as hash initialized to 0: 

hash1 = Hash[(date1..date2).collect { |v| [v, 0] }]

Now merge the two hashes: 

hash = hash1.merge(hash)

Do remember to merge hash with hash1 and not the vice-versa, because we want to overwrite the value of keys from hash to hash1

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

2 Comments

This works as intended. But wouldn't this extra ruby be overhead?
Yeah. It might be a little overhead in calculation. But if you ain't got any SQL way to do it, then this is the best method according to me. Still waiting to see if anyone comes up with the SQL way!!
2

Although it doesn’t seem to be well known, Ruby hashes have a “default value” feature that will help you do what you want in a better way. Another way of expressing what you’re trying to do is this:

Create a hash that gives 0 for any key within my data range unless I have stored some other value under that key.

You can accomplish exactly this using a block to establish the default value of the hash:

@data = Hash.new do |h, key|
   if (date1..date2).include?(key)
      0
   else
      nil
   end
end

Or, more succinctly:

@data = Hash.new {|h, key| (date1..date2).include?(key) ? 0 : nil }

Then load your data from the database and merge it into this hash:

@data.merge! User.where(…).group(…).count

Note: Your order() is irrelevant because you are grouping. It will probably get optimized away, but you can simply leave it off and get the same result.

Now if you do @data[foo]:

  • if foo is not comparable to a date you will get nil
  • if foo is outside the date range you will get nil
  • if foo is within the date range but no data was returned by your query you will get 0
  • if foo is a date with data in the database you will get the correct count

This method is preferable to pre-loading the hash because it will work efficiently (in time and space) even for very large date ranges. And arguably it is more straightforward too.

Improve this answer

1 Comment

Excellent use of efficient language features. I salute you sir.
2

I created a gem exactly for this - check out Groupdate.

Improve this answer

Comments

1

Perhaps, this is a bit difficult as if any user is not created on 16th May then there would be no record of 16th May as created_at in the DB and group by query result wont contain any value for 16th May.

Probably you will have to handle this in ruby. Or what you can do is check if a particular date is present as key in the hash_result, and if it is not present then user count is 0 by default.

Hope this helps :)

Improve this answer

2 Comments

Well I can always do that - modifying the result of the above query to get the required result. But I want to get by the SQL query itself.
I don't think it's possible with any DB query!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.