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?

Batt

looks like ransack translates search parameters into queries on the ActiveRecord ORM -- which would definitely fail unless you have a real column that the underlying DB can search on. – Eventually

Thanks for the comment @bdares. I have now managed to set ransacker :end do |r| in the model to this: Arel::Nodes::SqlLiteral.new("DATE_ADD(events.start_date, INTERVAL events.length DAY)"). Which produces the query: SELECT DISTINCT events.* FROM events ORDER BY DATE_ADD(events.start_date, INTERVAL events.length DAY) DESC LIMIT 30 OFFSET 0. That does work, but feels horribly 'hacky'. – Batt

@Batt - It's hard to see the full solution in your comment. Care to write out a full example so we can get a better understanding of the solution. Then you can accept your own answer. – Cleanly

@JavidJamae I've posted an answer which should make it a little clearer – Batt

Answer 1

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.

Batt