Ognjen Regoje bio photo

Ognjen Regoje
But you can call me Oggy


I make things that run on the web (mostly).
More ABOUT me and my PROJECTS.

me@ognjen.io LinkedIn

Sorting entries by difference to a date

#ooradee #rails #sql #technical

Sorting search results by the difference in number of days from a selected date works well.

In Rails it’d be accomplished like this:

scope :sorted_by_difference_to, -> (date) {
  select("items.*, ABS(DATEDIFF('#{date.strftime("%Y-%m-%d")}', parsed_parent)) as difference")
  .order(difference: :asc)
}

The query creates a new column that’s the absolute difference in days between the date parameter and a date the entries table contains, in this example completed_date. Then, it orders the results by that difference.

Use cases

I used this for quick search of when the user is viewing at entries of a particular day.

It turned out that showing only entries for the selected day was insufficient. So was only the past, or only the future, or a certain number of days in the future/past. But, with this approach nearby entries (regardless whether they’re future or past) had the same relevance and it ended up working quite well.

Another scenario where I thought this would be useful is searching a list of meetings or contacts with a specific client. As the difference increased the likelihood that the entries would be relevant decreased.

The meeting that happened two years ago doesn’t need to be shown on top. Neither does the the meeting that’s scheduled far into the future. But previous weeks’ meeting, or the one that is next week are often similarly relevant.

Improvements

Another way this could be used is by removing the ABS function and adding filtering by difference (i.e. where difference > -5 and difference < 5). It would be helpful in scheduling by seeing entries that have been recently completed and ones that are due soon letting the user effectively see the progress or timeline for a set of results. It would also be helpful where things might be scheduled in advance but are adjusted as things are completed.

A similar scope (or query elsewhere) could also be implemented to count hours or seconds. This entirely depends on the range of timestamps and the number of entries. The closer the entries the smaller the unit of difference.

One way this could be improved that I haven’t had the chance to try yet is to introduce a bias towards future or past. For example, if the difference was negative, instead of just getting its absolute value, it’d be multiplied by a coefficient with a bias: eg. -0.95 or -1.05 to introduce a 5% preference either way.