skarger

SELECT workouts.*
FROM workouts JOIN (
    SELECT max(id) AS id FROM (
        SELECT w.id, w.sport_id
        FROM workouts w JOIN (
            SELECT sport_id, max(created_at) AS created_at
            FROM workouts
            GROUP BY sport_id
        ) latest_by_sport
        ON w.created_at = w.created_at

skarger

SELECT DISTINCT ON (workouts.sport_id) workouts.*
FROM workouts
ORDER BY sport_id ASC, created_at DESC

skarger

sports = Sport.includes(:most_recent_workout).all

sports.each do |sport|
  puts sport.name

  puts sport.most_recent_workout&.notes || "No #{sport.name} workouts yet"
end

skarger

class Sport < ApplicationRecord
  has_many :workouts

  has_one :most_recent_workout, -> do
    merge(Workout.most_recent_by_sport)
  end, class_name: "Workout", inverse_of: :sport
end

skarger

# 1 query to get all 3 sports
sports = Sport.all

# N == 3 queries to get each sport's workouts
sports.each do |sport|
  puts sport.name

  puts sport.workouts.pluck(:notes).join("\n")
end

skarger

class Workout < ApplicationRecord
  belongs_to :sport

  scope :most_recent_by_sport, -> do
    from(
      <<~SQL
        (
          SELECT workouts.*
          FROM workouts JOIN (
             SELECT sport_id, max(created_at) AS created_at

skarger

SELECT workouts.*
FROM workouts JOIN (
   SELECT sport_id, max(created_at) AS created_at
   FROM workouts
   GROUP BY sport_id
) latest_by_sport
ON workouts.created_at = latest_by_sport.created_at
AND workouts.sport_id = latest_by_sport.sport_id

skarger

ID    USER_ID  SPORT_ID   NOTES             CREATED_AT
---------------------------------------------------------------
2     1        2          Second bike ride  2019-06-16 14:30:00
4     1        1          Second swim       2019-06-20 17:45:00

skarger

ID    USER_ID  SPORT_ID   NOTES             CREATED_AT
---------------------------------------------------------------
1     1        2          First bike ride   2019-06-15 10:15:00
2     1        2          Second bike ride  2019-06-16 14:30:00
3     1        1          First swim        2019-06-18 18:00:00
4     1        1          Second swim       2019-06-20 17:45:00

skarger

ID    NAME       TIPS
----------------------------------------------------
1     swimming   Practice swimming in open water.
2     cycling    Know how to fix a flat tire.
3     running    Find shoes that fit well.