Rails 4 where,order,group,count include zero's

问题:

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.

回答1:

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

回答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.

回答3:

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

回答4:

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 :)