First or last per user with ruby on rails

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 in this repo you will find 5 ways of doing it, two benchmarks that you can run to test for your use case, and two examples on how to associate the posts to the users.

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

3. Run the examples with ruby examples/<file name>. For example:

ruby example/00_example.rb

4. Change the seeds on db/seeds.rb and re-run ruby db/setup.rb to test different scenarios.

Inspiration

This example is based on a proposal of Steave Polito.

Active Record Playground

This example uses the Active Record Playground by bhserna