Ransack: Create a 'between' query with ransack?

[ricsrock]

Person.where(:created_at => [Time.now-60.days..Time.now])

Gives me the expected

SELECT `people`.* FROM `people` WHERE ((`people`.`created_at` BETWEEN '2012-04-03 02:42:08' AND '2012-06-02 02:42:08' OR `people`.`created_at` IN (NULL)))

How do I do this with ransack?

With MetaSearch, I did it using a multi-parameter field and a scope, but I can't seem to find how to do this with ransack.

Thanks.

[tonymarschall]

You can use lteq and gteq predicates:

<%= f.label :created_at_gteq %>
<%= f.text_field :created_at_gteq %>

<%= f.label :created_at_lteq %>
<%= f.text_field :created_at_lteq %>

[mwalsher]

+1. Th lteq and gteq approach doesn't work when you want to have a single date search field, say for searching created_at (which is a datetime field), especially if timezones come into play.

[radar]

Try out my by_star gem: https://github.com/radar/by_star.

[hillmandj]

Hey, though the 'between' issue is resolved it is not entirely clear how to create a search field for created_at and return nothing but records that exist on that day...are there any suggestions on how to do this?

[jonatack]

@hillmandj look here #34

[jonatack]

Here is how I do it (assume an Event model with a datetime field called date):

In the search form view partial:

<%= f.search_field :event_date_casted_date_equals %>

In the model:

ransacker :event_date_casted do |parent|
  Arel.sql('date(events.date)')
end

In config/initializers/ransack.rb:

Ransack.configure do |config|
  config.add_predicate 'date_equals',
    arel_predicate: 'eq',
    formatter: proc { |v| process_user_date_search_string(v).to_date },
    validator: proc { |v| v.present? },
    type: :string
end

Then create, if needed, a method like process_user_date_search_string to verify and format the user date input to make it convertible to a date with to_date. You could just start with formatter: proc { |v| v.to_date }, in the date_equals predicate in ransack.rb to get going.

[hillmandj]

Thanks, I just tried your code -- it seemed to work some of the time, but other times, I had to select a date one day ahead to get the values I wanted (and as a result it included other dates). Any idea why this is happening? I don't think I need to create a process_user_date_search_string, since I'm inserting it as '2011-02-24'

Is it possible that this has to do with the conversion of UTC time?

[hillmandj]

How would you query based on different time zones?

[jonatack]

@hillmandj dealing with time zones in your app is another issue entirely and has nothing to do with my code example or Ransack ;).

For queries: http://www.postgresql.org/docs/current/static/functions-datetime.html

This isn't hard advice, but you may want to keep everything in UTC (or use AT TIME ZONE 'UTC' in your queries), check what default time zone is set in config/application.rb, etc.

[hillmandj]

The default timezone is not UTC: UTC does not take into account DST (which is important to keep in mind when running a business sometimes)...wish there was an easy way to get around this :)

[jonatack]

Yes, there is a way. But this issue isn't the place to discuss it. Good luck!

[hillmandj]

Ok, can you point me in the right direction? (We don't use postgres) Should I create a new issue? It seems like these forums are the defacto documentation for Ransack...not trying to veer off topic, but it's pretty hard to find basic answers to these problems. There are a bunch of issues that reference that in itself. Not trying to be a downer, I actually think Ransack is really cool, which is why I wanna get this right, so I can use it :)

[hillmandj]

I solved this using:

  ransacker :created_at_two do |parent|
    Arel::Nodes::SqlLiteral.new("date(convert_tz(orders.created_at, 'UTC', 'EST'))")
  end

However...I think it would be VERY HELPFUL if ransack dealt with this in the gem itself. What if one day we decide to change to a different DB, for example? At that point I would not be able to use this CONVERT_TZ function (and would have to change all of my ransackers that used DB specific functions)...just food for thought :-)

[jonatack]

@hillmandj Your time zone problems have nothing to do with Ransack and everything to do with how you are using your database.

[hillmandj]

@jonatack it actually has nothing to do with the database. config.time_zone doesn't change how data is written to the db, it is still written in UTC time. It just makes AR perform a conversion when querying. If you are using AR on your views with a different time_zone configuration, but are querying/searching using Ransack it can be confusing. That is all -- it has nothing to do with Ransack, but it would be helpful if Ransack handled it...

[glaucocustodio]

If you have a datetime column and a date input like dd/mm/yyyy you can use this ransacker:

# your_model.rb
ransacker :created_on, formatter: proc { |value| Date.parse(value).strftime('%Y-%m-%d') } do
  Arel.sql("DATE(#{table_name}.created_at)")
end

Given the input date 01/10/2018, it will search like:

SELECT * FROM table WHERE DATE(table.created_at) = '2018-10-01'