Auxiliary-Statements.md

@@ -99,4 +99,69 @@ end
 ```
 
 You can change the name of the class used to create the statements in the 
-[`auxiliary_statement.exposed_class`](https://github.com/crashtech/torque-postgresql/wiki/Configuring#auxiliary_statement.exposed_class) config.
\ No newline at end of file
+[`auxiliary_statement.exposed_class`](https://github.com/crashtech/torque-postgresql/wiki/Configuring#auxiliary_statement.exposed_class) config.
+
+## Recursive
+
+Auxiliary statements can run with a `RECURSIVE` modifier composed of two parts. The first query, as a regular query and auxiliary statement, and then a second query, added after a `UNION`, which will occur consecutively until it does not bring any more records. [PostgreSQL Docs](https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-RECURSIVE)
+
+### Model
+
+Some assumptions are made in order to simplify the process of setting up a recursive CTE.
+```ruby
+# models/course.rb
+class Course < ActiveRecord::Base
+
+  recursive_auxiliary_statement :all_categories do |cte|
+    cte.query Category.all
+    cte.attributes title: :category_title
+  end
+
+end
+```
+
+In this simple setup, where no `sub_query` is defined and using a regular `connect` option, the subquery is built from the main query, which also suffers an alteration. So, by default, the way that the 2 queries are connected is through `:id => :parent_id`. That said, the first query receives a `WHERE categories.parent_id IS NULL` and the second query ends with a `WHERE categories.parent_id = all_categories.id`.
+
+You can change how the two are connected by setting the `connect` option, as in `id: :parent_id`, where the left side will be present in the query so it can be connected to the right side on the second query (plus the extra 'IS NULL' default statement for the first query). If the first query already has a condition with the right side of the connection, then the null condition won't be added.
+
+You can also set it to use a `UNION ALL` by calling `cte.union_all!`.
+
+#### The depth column
+
+```ruby
+cte.with_depth(column_name = 'depth', start: (depth_start_value = 0), as: (expose_name = nil))
+```
+
+This option will add a calculated `depth` column to your queries to show on which iteration the row was added. The `as` option allows you to expose the attribute to the main query if you want to grab its results. You don't need to set an alias to use it on `WHERE`, just reference the statement name and the column name, as in `where(all_categories: { depth: 1 })`.
+
+#### The path column
+
+```ruby
+cte.with_path(column_name = 'path', source: (path_column_source = :primary_key), as: (expose_name = nil))
+```
+
+This option will add a calculated `path` column to your queries to show the tracked followed until reaching the retrieved row (with itself included). It behaves similarly to the `depth` in the matter of the alias. The difference here is that the result will always be an array of strings (varchar). You can use an `ANY` operator to grab any record that contains a given id in the path: `where('? = ANY (all_categories.path)', 3)`.
+
+#### Setting up sub_query manually
+
+The `cte.sub_query` option works in the same way as the `cte.query`, which means it supports the same things (Relation, proc, and string). But once it is set, the connection must be defined manually in both definitions (`query` and `sub_query`). Be aware that the automatic setup of the `sub_query` uses multiple from to make sure records are loaded correctly, as in: `UNION SELECT * FROM categories, all_categories`.
+
+### Detached
+
+Recursive CTE is also available in its detached form through `ToruqeRecursiveCTE` and works similar to its normal form with the addition of the extra settings available only for recursive operations: `sub_query`, `connect`, `union_all`, `with_depth`, and `with_path`.
+
+You can combine the detached form and a plain query to load the recursivity result:
+```ruby
+class CoursesController < ApplicationController
+  def index
+    all_categories = TorqueRecursiveCTE.create(:all_categories) do |cte|
+      cte.query Category.all
+      cte.attributes id: :id, title: :title
+      cte.with_depth as: :depth
+      cte.with_path as: :path
+    end
+
+    @categories = Category.all.with(all_categories).from('all_categories')
+  end
+end
+```
\ No newline at end of file