Ognjen Regoje bio photo

Ognjen Regoje

I make things that run on the web (mostly).

More /ABOUT me.

And about /PROJECTS I've been involved in.

Email me at me@ognjen.io

me@ognjen.io Twitter LinkedIn Instagram Github

Sorting entries by difference to a date

Given a use case in which date is a big part of the context, sorting search results by number of days to that date works well.

In Rails it’d be accomplished like this:

scope :sorted_by_difference_to, -> (date) { select("entries.*, ABS(DATEDIFF('#{date.strftime("%Y-%m-%d")}', completed_date)) 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

The actual use case for which this was built was quick search where the context was a list of entries for 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 with a specific client. As the difference increased the likelyhood that the entries needed to be accessed decreased. So, 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’s soon are of higher (but relatively similar) relevance.


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.

#ooradee #rails #sql #technical