[ruby-on-rails] Can you do greater than comparison on a date in a Rails 3 search?

I have this search in Rails 3:

Note.where(:user_id => current_user.id, :notetype => p[:note_type], :date => p[:date]).order('date ASC, created_at ASC')

But I need the :date => p[:date] condition to be equivilent to :date > p[:date]. How can I do this? Thanks for reading.

This question is related to ruby-on-rails activerecord ruby-on-rails-3

The answer is


You can try to use:

where(date: p[:date]..Float::INFINITY)

equivalent in sql

WHERE (`date` >= p[:date])

The result is:

Note.where(user_id: current_user.id, notetype: p[:note_type], date: p[:date]..Float::INFINITY).order(:fecha, :created_at)

And I have changed too

order('date ASC, created_at ASC')

For

order(:fecha, :created_at)

If you hit problems where column names are ambiguous, you can do:

date_field = Note.arel_table[:date]
Note.where(user_id: current_user.id, notetype: p[:note_type]).
     where(date_field.gt(p[:date])).
     order(date_field.asc(), Note.arel_table[:created_at].asc())

Rails 6.1 added a new 'syntax' for comparison operators in where conditions, for example:

Post.where('id >': 9)
Post.where('id >=': 9)
Post.where('id <': 3)
Post.where('id <=': 3)

So your query can be rewritten as follows:

Note
  .where(user_id: current_user.id, notetype: p[:note_type], 'date >', p[:date])
  .order(date: :asc, created_at: :asc)

Here is a link to PR where you can find more examples.


If you aren't a fan of passing in a string, I prefer how @sesperanto has done it, except to make it even more concise, you could drop Float::INFINITY in the date range and instead simply use created_at: p[:date]..

Note.where(
    user_id: current_user.id,
    notetype: p[:note_type],
    created_at: p[:date]..
).order(:date, :created_at)

Take note that this will change the query to be >= instead of >. If that's a concern, you could always add a unit of time to the date by running something like p[:date] + 1.day..


Examples related to ruby-on-rails

Embed ruby within URL : Middleman Blog Titlecase all entries into a form_for text field Where do I put a single filter that filters methods in two controllers in Rails Empty brackets '[]' appearing when using .where How to integrate Dart into a Rails app Rails 2.3.4 Persisting Model on Validation Failure How to fix "Your Ruby version is 2.3.0, but your Gemfile specified 2.2.5" while server starting Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432? Rails: Can't verify CSRF token authenticity when making a POST request Uncaught ReferenceError: React is not defined

Examples related to activerecord

Empty brackets '[]' appearing when using .where Get only records created today in laravel How do you manually execute SQL commands in Ruby On Rails using NuoDB Rails 4 LIKE query - ActiveRecord adds quotes Rails create or update magic? CodeIgniter query: How to move a column value to another column in the same row and save the current time in the original column? Check if record exists from controller in Rails Codeigniter's `where` and `or_where` Codeigniter LIKE with wildcard(%) Codeigniter: does $this->db->last_query(); execute a query?

Examples related to ruby-on-rails-3

Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432? rake assets:precompile RAILS_ENV=production not working as required How do you manually execute SQL commands in Ruby On Rails using NuoDB Check if record exists from controller in Rails How to restart a rails server on Heroku? How to have a drop down <select> field in a rails form? "Uncaught TypeError: undefined is not a function" - Beginner Backbone.js Application Adding a simple spacer to twitter bootstrap How can I change cols of textarea in twitter-bootstrap? Rails: FATAL - Peer authentication failed for user (PG::Error)