Recently I’ve been working a lot with dates and tonight I had a hard time finding information about how to group a model by date when the model only have a timestamp column.
Lets pretend we have a user model with a created_at attribute which is stored as a timestamp.
If we want to plot a simple graph showing signups per date this is the way to extract them:
1
| User.count(:group => "DATE(created_at)") |
Thats all there is to it. From this you will get an OrderedHash containing the date and count of users for each date.
One small thing to remember if you are using PostgreSQL. You will need the DATE() function in any ORDER/SELECT statements because PostgreSQL will only select/order by fields thats in the GROUP BY statement and this is a pretty healthy behavior that many databases (no names here) don’t care about. Think about it. If you are grouping all users by date and use * as selector how would the database know which username or email address to display?
This is what happens if you try to group by created_at without DATE() ->
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| >> User.count(:order => 'created_at DESC', :group => ["DATE(created_at)"])
ActiveRecord::StatementInvalid: PGError: ERROR: column "users.created_at" must appear in the GROUP BY clause or be used in an aggregate function
: SELECT count(*) AS count_all, DATE(created_at) AS date_created_at FROM "users" GROUP BY DATE(created_at) ORDER BY created_at DESC
from /tmp/app/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:212:in `log'
from /tmp/app/vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb:507:in `execute'
from /tmp/app/vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb:985:in `select_raw'
from /tmp/app/vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb:972:in `select'
from /tmp/app/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in `select_all_without_query_cache'
from /tmp/app/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/query_cache.rb:62:in `select_all'
from /tmp/app/vendor/rails/activerecord/lib/active_record/calculations.rb:255:in `execute_grouped_calculation'
from /tmp/app/vendor/rails/activerecord/lib/active_record/calculations.rb:132:in `calculate'
from /tmp/app/vendor/rails/activerecord/lib/active_record/calculations.rb:130:in `catch'
from /tmp/app/vendor/rails/activerecord/lib/active_record/calculations.rb:130:in `calculate'
from /tmp/app/vendor/rails/activerecord/lib/active_record/calculations.rb:48:in `count'
from (irb):18 |
And this is the right way to do it:
1
2
| >> User.count(:order => 'DATE(created_at) DESC', :group => ["DATE(created_at)"])
=> #<OrderedHash {"2009-10-14"=>22, "2009-10-25"=>4, "2009-11-04"=>8, "2009-10-15"=>9, "2009-10-26"=>16, "2009-11-05"=>9, "2009-10-16"=>193, "2009-10-27"=>14, "2009-11-06"=>9, "2009-10-17"=>49, "2009-10-28"=>15, "2009-11-07"=>6, "2009-10-18"=>36, "2009-10-29"=>8, "2009-10-19"=>116, "2009-10-30"=>15> |
As you may notice the Hash does not look ordered. But if you loop over it you will get them in order:
1
2
3
4
5
6
7
8
9
10
11
12
| >> User.count(:order => 'DATE(created_at) DESC', :group => ["DATE(created_at)"]).each {|u| puts "#{u[0]} -> #{u[1]}" }
2009-11-07 -> 6
2009-11-06 -> 9
2009-11-05 -> 9
2009-11-04 -> 8
2009-11-03 -> 14
2009-11-02 -> 20
2009-11-01 -> 10
2009-10-31 -> 6
2009-10-30 -> 15
2009-10-29 -> 8
.. |
And thats how you group a timestamp field with just its date part.
Cheers!
As part of getting my SuppressValidations gem to load automatically when launching
script/console I took some time to configure my .irbrc file a bit and I thought I’d share it.
So here it is in all it’s glory:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
| require 'rubygems'
require 'irb/completion'
require 'irb/ext/save-history'
# Tell irb how much it should remember and where
# to save it's history
IRB.conf[:SAVE_HISTORY] = 100
IRB.conf[:HISTORY_FILE] = "#{ENV['HOME']}/.irb-save-history"
# Simple prompt
IRB.conf[:PROMPT_MODE] = :SIMPLE
# Auto indent code
IRB.conf[:AUTO_INDENT] = true
# This is only done when using the Rails console
if rails_env = ENV['RAILS_ENV']
# Setup a Rails specific prompt
rails_root = File.basename(Dir.pwd)
IRB.conf[:PROMPT] ||= {}
IRB.conf[:PROMPT][:RAILS] = {
:PROMPT_I => "#{rails_root}> ",
:PROMPT_S => "#{rails_root}* ",
:PROMPT_C => "#{rails_root}? ",
:RETURN => "=> %s\n"
}
IRB.conf[:PROMPT_MODE] = :RAILS
# This is only done when the irb session and
# rails are fully loaded (from Mike Clark)
IRB.conf[:IRB_RC] = Proc.new do
# Log ActiveRecord calls to standard out
ActiveRecord::Base.logger = Logger.new(STDOUT)
# Setup a [] alias for find so you can say:
# Post[1] instead of Post.find(1)
ActiveRecord::Base.instance_eval { alias :[] :find }
# Require and mixin the SuppressValidations gem
# (gem install polly-suppress_validations)
require "suppress_validations"
include SuppressValidations
end
end |
And here’s a sample session:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| $ script/console
Loading development environment (Rails 2.3.2)
test> User[1]
User Load (0.2ms) SELECT * FROM "users" WHERE ("users"."id" = 1)
=> #<User id: 1, username: "foobar", email: "foo@bar.baz", password_hash: "8c64cb44ad2caacb37eae831a1611d74fd9b280a", password_salt: "ca0103ad867a3992810778f4eb9179026ef54090", created_at: "2009-07-31 01:38:46", updated_at: "2009-07-31 01:38:46", role_type: nil>
test> User[1].save
User Load (0.4ms) SELECT * FROM "users" WHERE ("users"."id" = 1)
User Exists (0.2ms) SELECT "users".id FROM "users" WHERE ("users"."username" = 'foobar' AND "users".id <> 1) LIMIT 1
User Exists (0.1ms) SELECT "users".id FROM "users" WHERE ("users"."email" = 'foo@bar.baz' AND "users".id <> 1) LIMIT 1
=> true
test> def some_method
puts "Hello from some method!"
end
=> nil
test> some_method
Hello from some method!
=> nil |
Posted in
Rails,
Ruby at July 31st, 2009.
2 Comments
Tagged with
irb,
Rails,
Ruby. Written by:
Patrik Hedman
In my last post I showed how you can install my suppress_validations gem on a per-project basis but that’s a bit cumbersome. Wouldn’t it be nicer if it always was available in the console on every project?
I think it would, so here’s how you can accomplish just that:
Edit your ~/.irbrc (or create it if you don’t already have one):
1
2
3
4
5
6
7
8
| require "rubygems"
if ENV['RAILS_ENV']
IRB.conf[:IRB_RC] = Proc.new do
require "suppress_validations"
include SuppressValidations
end
end |
And thats it, you can now fire up script/console and start suppressing validations
1
2
3
4
5
6
7
8
9
10
| $ script/console
Loading development environment (Rails 2.3.2)
>> post = Post.new
=> #<Post id: nil, title: nil, body: nil, ...>
>> post.save
=> false
>> suppress_validations { post.save }
=> true
>> Post.last
=> #<Post id: 1, title: nil, body: nil, ...> |
Posted in
Rails,
Ruby,
gems at July 28th, 2009.
No Comments
Tagged with
gems,
irb,
Rails,
Ruby. Written by:
Patrik Hedman
Validations on ActiveRecord models can really get in the way while trying things out in the console sometimes so the other day when I stumbled over this railscast episode: http://railscasts.com/episodes/62-hacking-activerecord I thought, hey, with some modification that could easily be turned into a plugin.
Install it with:
1
| $ gem install polly-suppress_validations |
then add this to your config/environment.rb file:
1
| config.gem "polly-suppress_validations", :source => "http://gems.github.com", :lib => "suppress_validations" |
and then unpack it with:
or just install it as a plugin:
1
| $ script/plugin install git://github.com/polly/suppress_validations.git |
Now say we have a model like this:
1
2
3
| class Post < ActiveRecord::Base
validates_presence_of :title, :body
end |
Now lets enter script/console and include the SuppressValidations module
1
2
3
4
| $ script/console
Loading development environment (Rails 2.3.2)
>> include SuppressValidations
=> Object |
However, trying to create a new post without the required attributes still fails as this example shows:
1
2
3
4
| >> post = Post.new
=> #<Post id: nil, title: nil, body: nil, ...>
>> post.save
=> false |
To suppress validations just wrap the call to post.save in a suppress_validations block, like so:
1
2
3
4
| >> suppress_validations { post.save }
=> true
>> Post.last
=> #<Post id: 1, title: nil, body: nil, ...> |
Posted in
Projects,
Rails,
Ruby,
gems at July 20th, 2009.
No Comments
Tagged with
activerecord,
gems,
plugin. Written by:
Patrik Hedman
Today I had a really painful experience with rails InvalidAuthenticityToken. It turned out not to have anything to do with rails at all and there is where the painful part come in to play.
Read More…
Just before christmas I worked with one of our customers new server cluster or cloud if you may.
This cluster has production nodes and staging nodes and this post is about how to get capistrano to play nice with both production and the staging environment.
Read More…
Just released a new version of capistrano_colors. This is a total rewrite from the last version. This post show some of the bigger changes.
Read More…
Posted in
Programming,
Projects,
Rails,
Ruby,
gems at January 4th, 2009.
No Comments
Tagged with
capistrano,
colorize,
colors,
gems,
release,
Ruby. Written by:
Mathias Stjernström
Today I got the chance to give Ruby Enterprise Edition a try. We are currently working with a really fun new environment and you do not get better opportunities to try new stuff as when you have a complete new environment thats not in production. So we gave REE a shoot. When compiling REE with got in trouble really fast and this post is about the quick fix to solve it and some of our early performance results.
Read More…
Finally my friends at newsdesk have a blog. They have started off real good with a comparison of three ruby wrappers for Google charts.
I have great expectations from the newsdesk developers blog. If you are a Ruby and Rails developer you should definitely keep and eye on them!
Posted in
Life,
Rails,
Ruby at November 25th, 2008.
No Comments
Tagged with
blog,
charts,
google,
Rails,
Ruby. Written by:
Mathias Stjernström
Today one of our server got in trouble with an ACE encoded IDN domain. The problem seemed to be rails in combination with nginx’s X-Sendfile feature. The result became that when accessing the site trough the IDN domain x-senfile did not send any files….
Our quick and dirty solution (until we have found the source/or solved the problem) became redirecting all traffic from the IDN domain to the domain without strange-swedish-characters.
With nginx this is as simple as in most webservers just put the following code before you original server configuration in nginx.conf.
1
2
3
4
| server {
server_name xn--svenskdomn-y5a.se www.xn--svenskdomn-y5a.se;
rewrite ^(.*) http://www.svenskdoman.se$1 permanent;
} |
And Bowang! All requests to www.svenskdomän.se will be redirected to www.svenskdoman.se.
Domain names have been changed to protect the innocent 
Cheers!
Posted in
Hosting,
Rails at May 21st, 2008.
No Comments
Tagged with
Hosting,
nginx,
Rails. Written by:
Mathias Stjernström
