Recursive Queries in Rails

Common Table Expressions (CTEs) are a tool for DRYing up our SQL queries and also the only way to do recursive operations on our database. They allow us to model hierarchical data in our applications. Why not have such an advantage in our Rails apps while generating queries using ActiveRecord?

Using ActiveRecord for fetching single or multiple records is a piece of cake for most. However, sometimes the business demands us to generate complex queries to account for nested filters and sorting attributes. Sometimes the data model becomes hierarchical and generating queries in ActiveRecord for such cases can become difficult. People usually end up writing raw SQL queries and for some cases that’s totally fine. However, by using either Arel or an external gem such as https://github.com/GeorgeKaraszi/ActiveRecordExtended, we can benefit from the abstractions provided while utilising the necessary concepts.

Utilising common table expressions (CTEs) can help DRY the generated SQL (in case we decide to go with the raw version later on) and also opens the possibility of modelling recursive/hierarchical data with the help of recursive CTEs.

This talk aims to expose people to CTEs and their recursive variants and how they can get started by using them in their Rails projects. It would first build the intuition for the recursive SQL with simple examples. It would then show some examples that may be close to real world applications where we could use recursive CTEs. Discussions about how NOT to use CTEs (ex. as sequence generators) would also be done.

Example queries: https://gist.github.com/Animesh-Ghosh/2d8313575868a45a76b824943dc7208d Real world examples sample: https://mariadb.com/kb/en/recursive-common-table-expressions-overview/ (examples shown in the talk will be similar since recursive CTEs work really well for such scenarious)

I was introduced to RDBMSs and SQL during my schools days and I have been fortunate enough to get exposed to problems that required me to deep-dive into how databases usually execute SQL, different niche categories of SQL functions provided by the various database vendors (JSON functions, DATETIME functions, aggregate functions both across a table and across a window definition) working on financial reporting applications, generating ad-hoc reports for different teams (sales, business development). I hope to spread the knowledge I gained so that more people get excited about SQL. Since being introduced to Ruby on Rails I have tried to find ways to achieve the same query building experience using ActiveRecord.