Retrieving the last N ordered records with ActiveRecord

Here's a database of chat messages.

|  id  | created_at |
|   1  |   13:20    |
|   2  |   13:21    |
|   3  |   13:22    |
|   …  |   ……………    |
|  121 |   14:27    |
|  122 |   14:29    |
|  123 |   14:32    |

So, we're building a chat, and what we'd like to see is the 10 most recent messages, with the oldest of the bunch at the top, and the newest message at the bottom, something like: [<Message 113>, <Message 114>, <Message 115>, …, <Message 123>].

At first, you might think "I'll just sort all messages by created_at in ascending order, and take the last 10". OK, here's what that looks like.

Message.order(created_at: :asc).last(10) # => [<Message 113>, <Message 114>, …, <Message 123>]

Looks good? Yes? No. Have a look at the SQL:

 SELECT "messages".* FROM "messages"  ORDER BY "messages"."created_at" ASC

   |  id  | created_at |
-> |   1  |   13:20    |
-> |   2  |   13:21    |
-> |   3  |   13:22    |
-> |   …  |   ……………    |
-> |  121 |   14:27    |
-> |  122 |   14:29    |
-> |  123 |   14:32    |

What, no mention of 10 in our SQL query?! .last is not so clever. We end up loading all messages in our database to Ruby, maybe a few hundred thousand, and then we throw away all messages except for the last 10, what a waste.

Okay, how about using OFFSET? Let's try.

Message.order(created_at: :asc).offset(Message.count - 10).limit(10) # => [<Message 113>, <Message 114>, …, <Message 123>]        

Looks good? Yes? No. Let's look at the SQL:

 SELECT "messages".* FROM "messages"  ORDER BY "messages"."created_at" ASC OFFSET 112 LIMIT 10

     |  id  | created_at |
SKIP |   1  |   13:20    |
SKIP |   2  |   13:21    |
SKIP |   …  |   ……………    |
  -> |  113 |   14:13    |
  -> |   …  |   ……………    |
  -> |  123 |   14:32    |

A few important notes about this.

  1. OFFSET in SQL must be a positive number so we can't simply use -10 as our offset.
  2. ActiveRecord will try to coerce our offset to an integer using #to_i, so we can't pass a subquery as our offset, which leaves us with precomputed positive numbers only.
  3. What is our offset? It must be calculated ahead of time using Message.count, this is slow, and prone to race conditions if we get more messages in between our count and select.

Even if we disregard all the above points, have a look at the documentation for LIMIT/OFFSET in PostgreSQL: "The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET might be inefficient."

Oh, OK, so offset is off.

One more try. Let's sort this out with a new mindset. We change the order of our SQL, and reverse the thing in Ruby. That must work!

Message.order(created_at: :asc).reverse_order.limit(10).reverse # => [<Message 113>, <Message 114>, …, <Message 123>]

Looks good? Yes? Let's look at the SQL.

 SELECT "messages".* FROM "messages"  ORDER BY "messages"."created_at" DESC LIMIT 10

   |  id  | created_at |
-> |  123 |   14:32    |
-> |   …  |   ……………    |
-> |  113 |   14:13    |

Well, this is actually quite OK. We only retrieve 10 records from the database, which is what we want. Sure, the order of the messages is wrong which is kind of sad, but we can fix that later. Let's make a scope of this and call it a good day!

class << Message
  def in_order
    order(created_at: :asc)

  def recent(n)

There's a few downsides with this, can you spot it?

  1. We have to reverse it in Ruby.
  2. The return value is an Array, and not an ActiveRecord::Relation, since we force it with reverse.
  3. We can't merge an Array with other scopes.
  4. We can't chain additional SQL conditions to the end of our array to further filter the 10 results, e.g. Message.recent(5).where(…).

We can still do better!

What if I told you there is a way to reverse the result in SQL, and that there's also an OK way to do so with ActiveRecord? I'm sure you'd believe me after all of this, anything else would be cruel.

class Message
  class << self
    def in_order
      order(created_at: :asc)

    def recent(n)

    def endmost(n)
      all.only(:order).from(all.reverse_order.limit(n), table_name)

Looks good? Yes! Let's use this and have a look at the SQL, Message.recent(10):

SELECT "messages".* FROM (
  SELECT  "messages".* FROM "messages"  ORDER BY "messages"."created_at" DESC LIMIT 10
) messages  ORDER BY "messages"."created_at" ASC

   |  id  | created_at |
-> |  113 |   14:13    |
-> |   …  |   ……………    |
-> |  123 |   14:32    |

This is exactly what we want, and it has none of the downsides of reversing the results in Ruby. The final .recent method is our final implementation, and it works as expected.

The keen eye will notice that I extracted part of the logic into a general-purpose .endmost method. .endmost is what you want when you call .last: the last N records in the result set without having to retrieve all records from the database, and it works with any ordering. You can impose filtering before, Message.where(…).in_order.endmost(10), and afterwards to filter your final results as well Message.in_order.endmost(10).where(…).

Thanks for reading! I hope you found it as useful as I did!


Introducing Serial, a light-weight no-magic serialization library (for Ruby, and Rails)

Jonas and I created a serialization library recently while working on ProjectPuzzle, it's named Serial.

Serial will generate a Hash or an Array of hashes from an object of your choosing. It has a very small API surface, and is designed to be easy to reason about. It's suitable for where you'd use YourModel#as_json, ActiveModel::Serializers, or JBuilder. It could look something like this:

# app/serializers/person_serializer.rb
PersonSerializer = do |h, person|
  h.attribute(:url, account_person_path(person.account, person))
  h.attribute(:assignable, policy(person).assignable?)
  h.attribute(:skills,, person.groups, &GroupSerializer)

# app/controllers/api/people_controller.rb
include Serial::RailsHelpers
def index
  people = People.all
  render json: { people: serialize(people) }

You're very welcome to have a look, you can find it at, we'd love to hear what you think!


How to annoy all moose a little less (by building accessible websites)

A moose putting its tongue out

I have a close relationship to moose. Of all the good things this has brought me in life, one that really stands out is that it gave me my current job as a designer here at Elabs. This makes me thankful to moose, and I want to give something back.

As a designer who writes code, I make choices daily that impact how well a moose can percieve and navigate our web applications. A lot of the time, I make it very hard for them. I could do better.

I'm by no means an expert on moose accessibility, but recently I've been eating the brains of smarter people to gain their knowledge. I would like to share a few tips on how to make life a tiny bit easier for moose that surf the web.

As a coincidental bonus, these tips should improve accessibility even more for humans (and their different function variations).

I'll focus on some very basic things that many of us still get wrong, but is easy to get right.

Remember the semantics

A link is not a button. An <a> with href should link to another resource, while a <button> performs an action in the current view (such as submitting a form or opening a modal window). Buttons come with built in behavior that links don't have, like being activated by pressing space. Want something more discreet than a button for a minor action? Style it like a link.

Mind your headings. Be carfeul when you're building your heading structure, to make sure you're not skipping levels. Headers are the backbone of the document and a common way of navigating it with a screen reader. Don't pick heading level based on style or font size. If you want the look of a <h3> where a <h2> should be, try styling it with <h2 class="h3"> or similar. Also, Harry Roberts argues that your logo is an image, not a <h1>.

Placeholders are not labels. We hear this all the time, but still get this wrong sometimes. Many site search inputs still use placeholder text as their label (or nothing at all, and solely relies on the "Search" button to convey its function). If you need a compact search form, hide the label in a way that doesn't hide it from screen readers, like this .visually-hidden class does.

Is it content? Serve it to everyone.

Provide text alternatives. Until Google finishes their automatic image captioning algorithms, we're stuck with writing alt texts ourselves, whenever we use an image that qualifies as content (as opposed to styling). It's especially important when the image is a link, since screen readers will read out the link URL as title if no alt is present for the image.

Links should be self explanatory. Using "Click here" or "Read more" as link text makes the destination a mystery if you're navigating a page by jumping through its links, which is often what screen reader users do. Ambiguous links is the third most problematic item on the web (after CAPTCHAs and Flash) according to a WebAIM Screen Reader User Survey.

Color alone can't be trusted. Don't rely on just color to convey meaning, make sure there are labels or other visual clues to complement. Keep it in mind when styling states of interactive elements, such as :focus states of inputs or :hover states of links. Around 8% of human males and 100% of female and male moose have a color vision deficiency. What if it was your moose?

Apples of different colors to illustrate color vision deficiency types

Types of color vision deficiency (dichromacy)

Stay legible

Zooming should not break anything. Being able to zoom a page is important for individuals with poor vision (like... every moose), or just anyone tired of tiny text. Make sure your site doesn't break when zooming. Use relative units for font sizes and margins/paddings. Font sizes set in px zooms well in many browsers today, but not at all in IE (not even in IE 11 – it's by design). Remember there's a difference between zooming an entire page, with images and everything, and increasing the browser's font size. The latter is common if you're finding the web site's default font size hard to read.

Check your contrast. It's not just for elderly people. Sometimes we forget about low quality monitors, sun glare, tiny handheld screens, or a combination of them. Use Lea Verou's contrast ratio tool, the Sketch color contrast plugin, or ask your friendly neighborhood moose to have a look.

Key value

Some people and most cats hate mice. Try to make all parts of your website keyboard accessible. Tab through it and make sure there is a :focus state for every :hover state. Be extra careful with any custom widgets, especially modal windows.

Keyboard with apples as keys

Äpple Bluetooth Keyboard (the number one input device among moose)

Going further

These tips were absolute basics, they should be a part of every project. Still I cheat and I get lazy, and I know I'm not alone. These things are not that hard, they're just tweaks to your workflow.

Once you've managed to annoy moose and people a little less, go the next mile and learn how to do some real difference:


Has Microsoft become altruistic or is cooperation simply better than competition?

The market economy prides itself on its competitive nature. Through competition we’re promised better and cheaper products and services. I believe this to be true in countless cases. I also believe that we need to be better at considering both the benefits and the inefficiencies that the competitive model brings. As with any dogma it can do just as much harm as good unless constantly being evaluated from a case-to-case perspective.

Retrieved from

Consider for example what companies do to protect their investments in research and development. Companies are constantly suing each other over patents and spending billions on lobbying to make IP-laws stricter and more far-reaching. We buy this concept because we believe competition is more effective than cooperation. It’s been this way for so long that it’s hard to even imagine the alternative. Who knows what kind of cars we would have if all the car manufactures cooperated in their RnD. Maybe we would all have been driving the Tesla Model S ten years ago, maybe we would still be stuck in a Trabant, who knows?

Given this blog post being on a software development firm’s blog you’ve probably already figured out where this is going. Open Source is a large-scale, unconditional, more or less all-in choice of cooperation before competition, and it works! 59% of all web sites run on open source web servers. 95% of smart phones run on open source technology. 82% of users access the Internet using a web browser built on an open source foundation. With Microsoft’s recent release of the .NET framework as open source pretty much all major programming environments are now open source.

Retrieved from Illustration by Giulia Forsythe

In my experience open source projects tend to be numerous and diverse, allowing the user to choose a project that suits their needs. Often times they have large and helpful communities, answering questions in time zones all around the globe. Open source products are often free, and thus available to more users. Simply giving more people access to others’ creations can spawn innovation that would be lost in a closed environment.

As a software consultant I know that trust is key. When there is lack of trust there is legal overhead, misinformation and static control structures that prevent agility. These projects tend to be more expensive and deliver inferior results. Open source requires a whole bunch of trust. Put yourself in Microsoft’s shoes. Would you find it easy to “give away” technology that you’ve spent billions developing? You’d need to put your trust in people you’ve never met, hoping you will get back as much as you’ve given.

Open source in the software industry might be the most obvious example of trusting others to do something great(er) together. But in fact our entire civilization requires us to trust in each other. Without trust currency would be hugely inconvenient, e-commerce would be impossible and we would have no taxes to pay for services such as law, defense and education.

I don’t believe every sector would benefit from unconditional cooperation but I’m certain that there are vast possibilities to create better, cheaper, fairer, and safer products and services if we start trusting each other more!

Which sectors do you think would benefit from replacing competition with cooperation? How can the software industry benefit from even more cooperation? Or have we gone too far? Use the comment section below!


Refile: Fixing Ruby File Uploads

Six years ago, Merb was on the scene. I was one of its acolytes. I wanted a nice upload plugin for Merb. Since I had some experience in this area from writing (never popular) UploadColumn, I wrote one. It was called merb-upload, and my intention was for this to eventually become part of merb-more. You've probably never heard of merb-upload, but you may have heard of what it eventually became. It slowly grew Rails support, and to reflect the diversification, I renamed it CarrierWave.

It actually took a while for CarrierWave to become succesful, so it was the new kid for the long time, but it's actually only about a year younger than Paperclip. While both of these libraries, the two most popular for file uploads by far, have changed drastically over time, they are nevertheless both over six years old.

A lot has changed since then.

Over the years I've been contemplating what I would have done differently if I had to do it all over again.

  1. CarrierWave's killer feature is fixing file uploads to work even if validations failed and the form needs to be redisplayed. Unfortunately this has never worked reliably when files were uploaded to S3.

  2. Processing files on upload can be prohibitively expensive, and CarrierWave doesn't provide a good solution to this. When the size of an image should be changed CarrierWave requires re-processing all files. Dragonfly has a much better solution to this problem: processing images on the fly and serving them up directly, behind a CDN.

  3. Direct to S3 uploads are cumbersome. I find myself needing this time after time, and while there are bolt-on solutions, nothing really feels very solid.

  4. The internal design of CarrierWave is just far too flexible and strange. Uploader classes, while a nice relief from the monstrous configuration in model classes that other plugins require, are also quite brittle. I also found myself never actually using any of the insane flexibility that CarrierWave provides. It's really hard to figure out what actually happens inside CarrierWave.

  5. The integrations have changed. When CarrierWave was written, Ruby web frameworks were different than they were today. Rails 2.3 with support for Rack wasn't released until a year after its inception. Merb was a serious contender. As a result, there's a lot of boilerplate in CarrierWave which isn't really necessary.

All of these problems have been on my mind off and on again over the past couple of years. Even though I haven't been involved in the CarrierWave project in years, I still found myself unsatisfied with its current state, and with the status quo of file uploads in general.

So I wrote a new one. This is my third attempt at a file upload plugin, and I'm hoping that this time, maybe I got it right.

It probably isn't for everyone. It's far, far more opinionated than CarrierWave or Paperclip. I don't expect it to outright replace either. But if you're looking for something which is simple, where you can get direct to S3 uploads with the addition of a single config option, where you don't really have to think about how and where files are stored. This may be the one for you.

It's called Refile. Please check it out and let me know what you think. All feedback and especially all contributions are very welcome.