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

Question

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?

Accepted Answer

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.

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

Solution

Problem