Full TIL Post

Reading the postgreSQL's documentation, I found the WITH queries quite interesting, so I tried to use them for a common scenario: generate the breadcrumbs for the navigation of a storage system.

Instead of make a request to the database for every element inside the tree of the storage system in order to create the breadcrumbs, I make a single sentence to generate the list of elements involved in the breadcrumbs.

Let's pretend we have the classic relationship between a folder and its parent. We can generate the list we are talking about with a method like this (by the way, since I actually work with Elixir, this code is written in Elixir)

  def get_breadcrumb_elements(folder_id) do
    query = "WITH RECURSIVE aux AS (
      SELECT id, name, parent_id
      FROM folders
      WHERE id = #{folder_id}
    UNION ALL
      SELECT c.id, c.name, c.parent_id
      FROM aux x, folders c
      WHERE x.parent_id = c.id AND c.parent_id IS NOT NULL
    ) SELECT id, name FROM aux r ORDER BY id"

    case SQL.query(Repo, query, []) do
      {:ok, result} -> result.rows
      {:error, _} -> []
    end
  end

Then, once we have those elements, we can generate the HTML of the breadcrumbs with a method like this:

  def breadcrumbs(folder_id) do
    folder_id
    |> get_breadcrumb_elements()
    |> Enum.map(fn [_, id, name] ->
      link(name, to: whatever_route_path(YouApp.Endpoint, :show, id))
    end)
  end

And there you have it! Your new shiny breadcrumbs