Use PostgreSQL to generate your navigation breadcrumbs

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
About The Author
Iván González - Software Developer
Hi, my name is Iván (aka dreamingechoes). I'm a passionate software developer from the north of Spain, interested in all kind of technologies.