CODEWITHSUNDEEP
ruby-on-railsruby
Gareth Burrows
Gareth Burrows

Reputation: 1182

ruby query between two date parameters

I have a model called Result. I need to create a variable that returns all results that come in between a certain date range. On the Result model there is a field called date, and on a form I am capturing a start and end date as parameters and passing them back to the controller.

so if the user enters '01/01/2014' in the startdate parameter and '01/01/2015 in the parameters I need to return all results where the date is between this range.

When the user pressers a "filter" button the parameters end up being captured as variables startdate and enddate

I tried this but it doesn't seem to work

@results = Result.where("date >= ? and date <= ?", startdate, enddate")

I then looked at the resulting SQL and thought it needed to be this

@results = Result.where("date >= ? and date <= ?", '#{startdate)', '#{enddate}')

Any ideas?

Thanks

Upvotes: 2

Views: 3817

Answers (3)

Gareth Burrows
Gareth Burrows

Reputation: 1182

Thanks to everyone for the advice. This was my solution in the end.

Firstly, ensure only valid dates can be entered in the form by using datepicker, which looks like this...

  $(function(){
    $('#startdate').datepicker({
      changeYear: true,
      dateFormat:"dd/mm/yy",
      defaultDate: "#{Date.today.day}/#{Date.today.month}/#{Date.today.year}",
      change: function() {
        var isoDate = this.getValue('yyyy-mm-dd');
        $(this).attr('data-value', isoDate);
      }
    });
$('#enddate').datepicker({
  changeYear: true,
  dateFormat:"dd/mm/yy",
  defaultDate: "#{Date.today.day}/#{Date.today.month}/#{Date.today.year}",
  change: function() {
    var isoDate = this.getValue('yyyy-mm-dd');
    $(this).attr('data-value', isoDate);
    }
  });
});


%form{:action => employee_results_path(@employee), :class => 'navbar-search pull-left'} 
  .field
    %input{:type =>'text', :placeholder => params[:startdate] != nil ? params[:startdate] : "from date", :id => 'startdate', :name => "startdate", :style => 'width: 100px'}
    %input{:type =>'text', :placeholder => params[:startdate] != nil ? params[:enddate] : "to date", :id => 'enddate', :name => "enddate", :style => 'width: 100px'}

then, in the controller capture the parameters as variables and Parse them as dates

sdate = Date.parse(params[:startdate])
edate = Date.parse(params[:enddate])

Then use the helpful solution I recieved in this question

@results = Result.where(:date => sdate..edate)

which worked a treat. Thanks to everyone to helped me learn!

Upvotes: 1

manu29.d
manu29.d

Reputation: 1568

As @MrYoshiji's also suggests, one thing you should take care in such a scenario is that both your startdate and enddate are either Date, DateTime or Time objects. Else, the comparison will fail.

1.) A shortcut would be to apply a datepicker in to your input tag. You can specify the format in the initializer for the datepicker.

2.) Use a date/time tag rails form helper.

3.) Use a regex to ensure a correct format.

And when it passes all your validations, then do:

@results = Result.where(date: Date.parse(startdate).beginning_of_day..Date.parse(enddate).end_of_day)

Also you can add a rescue at the end to ensure it does not go to a 500 page.

At the end of the query add a rescue Result.all (in case of ALL-ELSE-FAILED)

Yes, and make sure you get the timezones right. Default for postgresql is UTC.

Upvotes: 2

Arup Rakshit
Arup Rakshit

Reputation: 118289

You can write as

@results = Result.where(:date => startdate..enddate)

But before use the query, startdate and enddate must need to convert to a Date object.

Upvotes: 0

Related Questions