The one thing WITH couldn't do

The junior course "ActiveRecord: CTE basics" closed its first lesson with a promise. It named what it left out: a CTE that references itself, WITH RECURSIVE, for tree- and graph-shaped data such as an org chart or "a comment thread's replies". It called itself "deliberately the non-recursive half". Its seven lessons covered naming a subquery, joining it, chaining one CTE into another, and a materialization myth. None needed a query to reference its own output. This course keeps the promise, against the table that lesson named.

A comment thread is this app's one self-referential table

Community::Comment is the only model in this app where a row points to another row of the same kind:

belongs_to :parent, class_name: "Community::Comment", optional: true
has_many :replies, class_name: "Community::Comment", foreign_key: :parent_id, dependent: :destroy

A thread under a lesson is a tree: a top-level comment, its replies, and in principle replies to replies, as deep as anyone keeps typing. This app's code stops that at one level of replies, on purpose:

def single_level_of_replies
  errors.add(:parent, :invalid) if parent&.parent_id.present?
end

A reply may set its own parent_id, but a reply to a reply is rejected, because its parent would already have a parent_id. So comment threads here are two levels deep, always, by a validation the app's developers chose to write, not by anything the database enforces.

Community::LessonThreadQuery, which loads a thread, has the same shape. One query fetches the top-level comments, a second fetches every reply to any of them, and Ruby joins them back together with group_by(&:parent_id). Two queries, two levels and no recursion, because two levels is all this app's data can have.

The question this app doesn't have to ask

Set single_level_of_replies aside for a moment, and imagine the app allowed replies of any depth, as most discussion platforms do. LessonThreadQuery's two-query approach stops working the moment depth stops being fixed at two. Replies to replies would need a third query and the next level a fourth. That's one query per level, with no way to know in advance how many levels exist.

A recursive CTE is Postgres's answer to that problem. It's one query, written once, that keeps joining a table to itself until it runs out of rows, however deep the tree turns out to be. This app's validation means it will likely never need one for comments. The mechanism is real, though, and the shape of the problem recurs in schemas this app doesn't have. It's self-referential rows, unknown depth, and a wish for one query instead of one per level. Think of org charts, category trees, a permissions hierarchy, or threaded replies without this app's one-level rule.

This course builds the recursive CTE against community_comments, this app's real table, with its real columns and foreign key. It lifts the depth limit only inside disposable test data, written to prove each claim and cleaned up afterwards. It never touches a comment a learner posted.

Key takeaways

• Community::Comment is this app's only self-referential model: belongs_to :parent and has_many :replies both point at Community::Comment itself.
• A validation, single_level_of_replies, caps comment threads at two levels. The database schema places no such limit; the cap is application logic, not a constraint.
• Community::LessonThreadQuery loads a two-level thread with two queries and a Ruby group_by. That only works because the depth is fixed and known in advance.
• A recursive CTE answers a different question: how do you read a self-referential table in one query when the depth isn't fixed? This course builds that query against the real table, using disposable test data to go past the depth limit safely.