How to model nested categories?

Hello

Did you see this library GitHub - Byzanteam-Labs/hierarch: Tree structure & hierarchy for ecto models with ltree(Postgres) · GitHub?
It is based on ltree postgres extension. I didn’t try it, but I’d try if I have this kind of requirements

Thanks, seems great. I will have a look at this.

I’d imagine the database table (let’s call it categories) would have a reference to a parent category as a foreign key, parent_category_id.

If parent_category_id is NULL, it has no parent.

The problem with this is the need of potentially multiple joins on the same table and we dont know how many joins we need. Or potentially multiples chained requests.

Another way is to use a nested set model, using lft and rgt additional columns.

https://www.sciencedirect.com/topics/computer-science/nested-set-model

Postgres can do recursive common-table expressions if you really want, but it’s worth considering carefully exactly how your application will use the nested categories.

• how often are new categories added? Some representations will make this expensive
• how often do queries like “in this category and all its subcategories” occur? Some representations will make THAT expensive

Poking around in Hex turns up some candidates:

• Arbor uses CTEs to traverse parent_id chains
• Ancestry uses materialized paths; each record stores the full “path” through the hierarchy (a record in Subsubcategory D would store “A/C/D” or similar)
• as_nested_set uses nested sets to make answering “and all subcatgories” questions faster
• Hierarch linked up-thread by @fuelen uses a Postgres extension to offload some of the complexity to the database

Thank you.
I think i will remodel my app allowing only two levels to apply the KISS principle
I maybe will use one of these great libraries in the future.

I just did this for a project I’ve been working on. I ended up settling on closure_table.

The biggest challenge was building the proper data structure required by the admin front-end widget to use drag-n-drop etc.

I suspect it’s paid work and thus confidential but if it accidentally is not then I’d absolutely love to see the code. Sounds like it’s extremely handy.

It’s paid work but not confidential. If I get some free time, I’ll see if I can put together something.

As it stands, the code is still in a “first pass” state where I got things working the way I needed but had to move on due to time constraints, so at this point it’s not code I’d particularly proud of showing off. The idea was to circle back when things calmed down and have another pass at it