This article, briefly covers the idea of using the Active Record in Ruby on Rails with to create a subqueries to improve searches of a PostgreSQL database. An example where a subquery in SQL could be useful is:
We have a table called comments which has three columns: author, comment_text, and created_at.
The goal is to alphabetically sort the most recent 100 comment, less than six months old.
A snippet of SQL to solve this problem is below (for reference, I’m using PostgreSQL 11.3):
We first create a query which:
Finds the 100 most recent comments, less than six months old.
Then we create another query which:
Sorts the 100 most recent comments alphabetically.
Overall, this seems simple enough. To mimic it in Ruby using Active Record, we do the following:
Results are the same as above, and follows the same pattern. Obviously, this can be useful as you can continue to use Active Record (vs raw SQL) to continue to do development.