Ecto - counting nested associations recursively

Question

I have the following schema:

defmodule MyApp.Folder do
  use Enterprise.Web, :model

  schema "folders" do
    has_many(:contracts, MyApp.Contract)
    has_many(:child_folders, MyApp.Folder, foreign_key: :parent_id)
  end
end

As you can see, each folder can have recursively many child folders each having their own child folders and so on. In my folder controller, I want to count the total number of contracts contained in each folder and all of the contracts in its child folders and so on.

Say, I have a folder named root. If I want to count the number of contracts at the top level of the folder, I can just simply call length(root.contracts). However, I still haven't taken into account root's child folders and the number of contracts in each child folder and if each child folder descends into a tree of descendant folders and their contracts.

Answer 1

You'll need Recursive Common Table Expressions for problems such as these, Ecto does not natively support those yet (see: https://github.com/elixir-ecto/ecto/pull/2757). We'll have to use fragments (https://hexdocs.pm/ecto/Ecto.Query.html#join/5-joining-with-fragments)

You have not mentioned the version of the db you are using but the latest versions of Postgres/Mysql/MariaDb all support CTE's. I also presume Ecto 3.

Contract 
|> join(:inner, [c], f in fragment("(
  WITH RECURSIVE RecursiveFolders AS (
   SELECT
      F.id,
      F.name,
      F.parent_id as parent_id
   FROM
      Folders F
   WHERE
      F.id = ?
   UNION
   SELECT
      F.id,
      F.name,
      F.parent_id
    FROM
      Folders F
    JOIN RecursiveFolders C ON C.id = F.parent_id
   )
   SELECT
     *
   FROM
   RecursiveFolders

)", root_id), on: c.folder_id == f.id)
|> select([c], count(c.id))

To explain, the recursive CTE returns all rows with the root_id and its children, we join the contracts on these rows (I assume a folder_id column on the Contract schema). Lastly we do make Ecto return a count all returned rows.