Write complex SQL with sub query easily in Rails

#rails #ruby #sql #postgres

In some case, you have to write SQL with sub query clause. For example, when you use group by or window functions.

You can write it in raw SQL. But, if you have a helper method like this:

class ApplicationRecord < ActiveRecord::Base
   def self.from_subquery(relation)
     from("(#{relation.to_sql}) as #{table_name}")
   end
 end

you can write this nested query in ActiveRecord:

class RestaurantViewLog < ApplicationRecord
   def self.history_for_user(user)
     from_subquery(
       from_subquery(
         select('*', 'lead(restaurant_id) over (partition by user_id order by id) as next_restaurant_id')
           .where(user_id: user.id)
       )
       .where('next_restaurant_id is null or restaurant_id <> next_restaurant_id')
     )
   end

It can be composed with other conditions.

RestaurantViewLog
  .includes(:restaurant)
  .history_for_user(user)
  .order(id: :desc)
  .limit(20)

The raw sql of RestaurantViewLog.history_for_user(user) is below:

SELECT
  "restaurant_view_logs".*
FROM (
  SELECT
    "restaurant_view_logs".*
  FROM (
    SELECT *, lead(restaurant_id) over (partition by user_id order by id) as next_restaurant_id
    FROM "restaurant_view_logs"
    WHERE "restaurant_view_logs"."user_id" = 1
  ) as restaurant_view_logs
  WHERE (next_restaurant_id is null or restaurant_id <> next_restaurant_id)
) as restaurant_view_log