A Matter of Order

Many times we encounter an apparently simple problem: I need this list of items to be ordered by X attribute. How do I do it?

Now, for many cases it’s just a matter of sorting or, preferably, adding a call to order in your ActiveRecord query. For example:

User.order(:name)

But, what if I want to order first by a given status, say, a done todo item (yes, that was my actual case), and if the item is done, it should be ordered by its completion date, if not it should be ordered by its creation date.

Oh, and grouped by the different lists of todos that I have.

In my ignorance, I actually thought I’d have to use Ruby methods or delegate this work to the frontend (ew). And, indeed, if I wanted something like what JIRA does where you can reorder items and expect this order to be preserved over reloads and even over different sessions, then yes, I believe a more complex setup would be required.

However, I just want this specific ordering. It will never change. At least not in the forseeable future.

At that point I started to wonder: “SQL is based off of relational algebra, which is what we use to operate on sets that have, as the name says, some relation between them. Surely mathematicians solved this problem and surely a very clever fellow implemented this into SQL and surely ActiveRecord also allows me to do this, right? Right?”

Here I wish I could use the old Star Wars meme, but also to my delight, it turns out that ActiveRecord does allow me to do this.

First thing that I did not know. Or at least didn’t know it worked this way (again, ignorant fool): The order method will sort on multiple columns preserving any previous ordering that was done.

So if I do:

Todo.all.order(:done, :completed_at)

It will order the todos by their completion status and then order those two subsets by their completion date.

Again, no news here if you’re familiar with this or even actually know how SQL ORDER statements work.

But what I want is to sort one subset by a given column and the other by another column. How to achieve this?

Well, now we do need to use SQL. Or at least I’m not sure how to do this with ActiveRecord’s API. As we all know, we can provide raw SQL to ActiveRecord methods. It turns out that you have conditional statements in SQL and for what I want this is the way it would be written:

ORDER BY list_id ASC, done ASC, CASE WHEN done = false THEN created_at ELSE done_at END

The first part, the ORDER BY is doing what I explained before: orders by list_id, ascending, and then orders each subset by done, ascending. The second part is what was even more interesting to me: it will test in each of the subsets from the last ordering whether done is false. If it is, then it will order by created_at. If not, it will order by done_at.

Finally, because I want to render these todos by list subsections I need to add an includes(:list) to the query and so that the view can more easily create the subsections with the proper list title, I use Enumerable#group_by to make a hash of List and todo arrays and I can just iterate over the keys and render each summary with the list title and its corresponding todos.

Now, this is what the full query looks like (I just put the whole thing in a scope):

scope :todays_work, ->(user) do
  where(user: user)
    .where(done: false).or(Todo.where(done: true, done_at: Date.today.all_day))
    .order(:list_id, :done, Arel.sql("CASE WHEN done = false THEN created_at ELSE done_at END"))
    .includes(:list)
end

And in the controller I call the group_by(&:list) just to separate ActiveRecord queries from Ruby method calls. And then, in the view, instead of this eyesore:

<% @lists.each do |list| %>
  <% unfinished_in_list = @unfinished_todos[list] || [] %>
  <% completed_in_list = @completed_todos[list] || [] %>

  <details class="border border-gray-300 rounded-lg bg-white" <%= 'open' if unfinished_in_list.any? || completed_in_list.any? %>>
    <summary class="cursor-pointer px-4 py-3 font-semibold text-lg hover:bg-gray-50">
      <%= list.name %>
    </summary>
    <div class="px-4 pb-4 space-y-4">
      <div id="unfinished_todos_list_<%= list.id %>" class="space-y-4">
        <%= render unfinished_in_list %>
      </div>
      <div id="completed_todos_list_<%= list.id %>" class="space-y-4">
        <%= render completed_in_list %>
      </div>
    </div>
  </details>
<% end %>

I can just have one todo list to rule them all:

<% @lists.each do |list| %>
  <details class="bg-white" <%= 'open' if @todos[list].any? %>>
    <summary class="cursor-pointer px-4 font-semibold text-lg hover:bg-gray-50">
      <%= list.name %>
    </summary>
    <div class="px-4 pb-4 space-y-4 flex flex-col gap-2">
      <div id="todos_list_<%= list.id %>" class="flex flex-col gap-2">
        <%= render @todos.fetch(list, []) %>
      </div>
    </div>
  </details>
<% end %>

So much better. Notice I even use the fetch method just to get rid of the assignments I added earlier for readability. It’s the gift that keeps on giving.