128

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.

ben
  • 29,229
  • 42
  • 124
  • 179

5 Answers5

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

or you can also convert everything into the SQL notation

Note.
  where("user_id = ? AND notetype = ? AND date > ?", current_user.id, p[:note_type], p[:date]).
  order('date ASC, created_at ASC')
Simone Carletti
  • 173,507
  • 49
  • 363
  • 364
  • 2
    is that safe ? i mean if p[:date] came from user input, can it cause an SQL injection ? – MhdSyrwan Jul 08 '12 at 14:55
  • 7
    It's safe because of `where()`. Using `where()` automatically escapes the input. – Simone Carletti Jul 08 '12 at 17:37
  • 34
    Simone's comment isn't entirely true; `where()` automatically escapes the input when it's used in the format shown above with question-marks in place of variables, with them listed afterwards in the function call. It is NOT safe to use it in this way: `Note.where("date > #{p[:date]}")` – bdx Jul 02 '13 at 05:04
  • The safe ways are either with an array or with a hash – bigpotato Dec 16 '13 at 16:41
  • 4
    Also worth noting, using `where("user_id = ?",current_user.id)` is riskier than `where(user_id: current_user.id)` in cases where you merge models which both have a `user_id` field. Using the raw SQL notation means you need to include the table clarifications yourself, eg: `where("notes.user_id = ?",current_user.id)`. – DreadPirateShawn Jul 13 '14 at 10:03
  • 1
    You should be careful with this, because time zone. When you use where with "" Rails go directly to database, and in the database the date are saved in UTC, but you should be in UTC + 5 for example, and this will not correct. So ensure to convert the p[date] to your current UTC before do it – Paulo Tarud Sep 22 '17 at 19:34
72

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())
Sarah Vessels
  • 30,930
  • 33
  • 155
  • 222
  • 2
    For some reason, I was getting an error with a column not being found using Simone's "where" method on a PostgreSQL server but it worked in SQLite. Your method worked on both. – plackemacher Dec 21 '12 at 22:45
5

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)
sesperanto
  • 177
  • 2
  • 7
1

Update

Rails core team decided to revert this change for a while, in order to discuss it in more detail. See this comment and this PR for more info.

I am leaving my answer only for educational purposes.

new 'syntax' for comparison in Rails 6.1 (Reverted)

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.

Marian13
  • 7,740
  • 2
  • 47
  • 51
  • The PR was reverted so this will not work for now in Rails. A [new PR](https://github.com/rails/rails/pull/39863) for this in ongoing for now. – Paul B. Jun 01 '21 at 12:59
1

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

jmad8
  • 641
  • 6
  • 5