🔗 Masterclass common table expressions CTEs) in SQL: from theory to practice with Elixir Ecto

Think about your everyday online life. Have you ever been deep in an email conversation, where each message is a reply to a previous one, creating a long chain? Or maybe you’ve seen a lively discussion on X (the app formerly known as Twitter), where people reply to comments, and then others reply to those replies, building a huge, sprawling chat.

These common online experiences show us a tricky but fascinating type of data: information that’s deeply connected, like branches on a tree. It’s not just emails and social media; imagine a company’s structure (who reports to whom), or products organized into main categories and then smaller sub-categories.

The big question for us, as programmers, is: how do we get this kind of linked data out of our databases in a smart and easy way? If we don’t know how many levels deep the connections go, a simple way of linking tables won’t work.

This is where a special trick in databases called Common Table Expressions (CTEs) comes in, especially the “recursive” kind. These are like magic tools that help us follow these deep connections. Even though CTEs are a database feature, Elixir, with its Ecto tool, now has a really clean way to use them right in your Elixir code. In this article, we’ll explain what these “recursive CTEs” are, why they’re perfect for finding these connected…