loading...

Write complex SQL with sub query easily in Rails

uiur profile image Zato Updated on ・1 min read

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

It can be composed with other conditions.

RestaurantViewLog
  .includes(:restaurant)
  .history_for_user(user)
  .order(id: :desc)
  .limit(20)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

Discussion

pic
Editor guide
Collapse
cescquintero profile image
Francisco Quintero 🇨🇴

Interesting. What raw SQL does it produce?

Collapse
uiur profile image
Zato Author

Thanks for a comment! I've added it to the entry.

Collapse
cescquintero profile image