Write complex SQL with sub query easily in Rails

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

Note: Here the nested query is necessary to filter rows with the result of window function next_restaurant_id.

Blog

Write complex SQL with sub query easily in Rails

zato

Join Our Newsletter. No Spam, Only the good stuff.

Related