Search code examples
ruby-on-railsruby-on-rails-3.2ransack

Rails & Ransack - Sorting/searching based on a definition in the model

Say for example I have an event with start_date and length (as integer representing days).

In the model I define end_date as start_date + length.days very simply as you would expect:

def end_date
  start_date + length.days
end

All works fine in the template, I can use event.end_date to display the start date plus however many days length was set to, however, I want to now order the events by the end date using Ransack.

The sort link for start_date looks like this: <%= sort_link @q, :start_date, "Start" %>

If I try the same for end_date (<%= sort_link @q, :end_date, "End" %>) it unfortunately silently fails as I presume it's looking for end_date as a column in the table and not finding it.

Am I just being stupid or am I trying to do something Ransack simply wasn't made to do?

Solution

  • Just for the benefit of anyone wanting to see how I did it:

    In the model where I defined end_date

    def end_date
  start_date + length.days
end

    I then added a 'ranksacker' method for :end_date

    ransacker :end_date do |r|
   Arel::Nodes::SqlLiteral.new("DATE_ADD(`events`.`start_date`, INTERVAL `events`.`length` DAY)")
end

    I honestly don't know if this is the best/right way to do the above clause, but I'm far more familiar with SQL than with Ruby/Rails so it worked for me being so open.

    What that effectively does is create a piece of SQL that orders a query by the same definition as end_date (start_date + length)

    I can now in the template use the following code for a sort link (which allows Ransacker to handle the ASC/DESC and not interfere with wil_paginate etc etc):

    <%= sort_link @q, :end_date, "Event End Date" %>

    When you click that link you'll get a query along the lines of:

    SELECT DISTINCT events.* FROM events ORDER BY DATE_ADD(events.start_date, INTERVAL events.length DAY) DESC LIMIT 30 OFFSET 0 - All created and handled by Ransacker on a column in your table that doesn't even exist!

    Again, a quick disclaimer, I've been learning Rails and Ruby for about 2 weeks now, coming from a background of PHP so I am in NO WAY an expert and this could be horribly inefficient or there could be a much better 'rails way' that I simply do not know about.