CODEWITHSUNDEEP
mysqlruby-on-railsrubyactiverecord
ZHH
ZHH

Reputation: 99

SQL where to query in Rails

When I type:

User.where(:created_at => "2015-08-06".."2015-08-07").where("updated_at > created_at + 1.day").count

Error Returned:

SELECT COUNT(*) FROM `users` WHERE `users`.`created_at` BETWEEN '2015-08-06' AND '2015-08-07' AND (updated_at > created_at + 1.day)ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'day)' at line 1: SELECT COUNT(*) FROM `users`  WHERE `users`.`created_at` BETWEEN '2015-08-06' AND '2015-08-07' AND (updated_at > created_at + 1.day)

from /home/guinness/shared/bundle/ruby/2.1.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:in query'

What's wrong with this? As you know I want to know how many users retained after 1 day of their sign up.

Upvotes: 0

Views: 57

Answers (2)

Ankita Tandel
Ankita Tandel

Reputation: 202

Try it like this

User.where(:created_at => "2015-08-06".."2015-08-07").where("updated_at > ?", created_at + 1.day).count

Upvotes: 2

Max Williams
Max Williams

Reputation: 32933

1.day is ruby syntax, not sql. try this:

 User.where(:created_at => "2015-08-06".."2015-08-07").where("updated_at > DATE_ADD(created_at, INTERVAL 1 DAY)").count

see http://www.w3schools.com/sql/func_date_add.asp

Upvotes: 4

Related Questions