Benito Serna Tips and tools for Ruby on Rails developers

A tool and examples to help you implement the first or last per record in rails with one query

May 31, 2022

Have you ever needed to get the most recent post for each user in rails, but didn’t know how to do it without using map?

Or maybe something similar, like:

Here is an example of a simple way to do it when you can use the id column to sort the records.

class Post < ActiveRecord::Base
  belongs_to :user

  scope :first_per_user, -> {
    where(id: select("min(id)").group(:user_id))
  }

  scope :last_per_user, -> {
    where(id: select("max(id)").group(:user_id))
  }
end

puts Post.first_per_user
# => SELECT "posts".*
#    FROM "posts"
#    WHERE "posts"."id"
#    IN (SELECT min(id) FROM "posts" GROUP BY "posts"."user_id")

puts Post.last_per_user
# => SELECT "posts".*
#    FROM "posts"
#    WHERE "posts"."id"
#    IN (SELECT max(id) FROM "posts" GROUP BY "posts"."user_id")

But here you will find a repo, with:

You can find the repo in: bhserna/first_or_last_per_user.

The 5 methods are:

The 2 benchamarks are:

The 2 examples on how to associates the posts to the users are:

How to run the examples

  1. Install the dependencies with bundle install.

  2. Database setup - run the command:

ruby db/setup.rb
  1. Run the examples with ruby examples/<file name>. For example:
ruby example/00_example.rb
  1. Change the seeds on db/seeds.rb and re-run ruby db/setup.rb to test different scenarios.

Active Record Playground

This repo uses the Active Record Playground that you can also use to play with some Active Record models in isolation and without creating a full rails app.

Inspiration

This example is based on a proposal of Steave Polito, but trying to implement the samething with one query.

Related articles

Download a free ebook to learn the basics of n+1 queries on Rails basics

Learn just enough fundamentals to be fluent preloading associations with ActiveRecord, and start helping your team to avoid n+1 queries on production.