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:

• The first or last comment for each post
• The first or last payment for each customer
• The first or last review for each customer

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:

• 5 different methods to implement the “first or last per user”.
• 2 benchmarks, memory and ips, that you can run to test which method is better for your use case.
• 2 examples on how to associate the posts to the users, and do something like user.last_post or posts[user].

You can find the repo in: bhserna/first_or_last_per_user.

The 5 methods are:

• Using min() and max()
• Using distinct_on
• Using distinct_on with arel
• Using a lateral join
• Using a window function

The 2 benchamarks are:

• Memory benchmark code
• Iterations per second benchmark code

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

• A has one association
• A preload object

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

• Joins does not preload
• How can I determine if my n+1 queries fix can hurt the performance?
• Touch + fragment caching to avoid race conditions saving computed values
• Use with_lock to handle race conditions saving computed values