How to model nested hierarchical data?

Help
, ,
1

Hello all

I would like to manage categories for a shop application. Each category can have subcategories, which in turn can have subcategories.

Currently I have modelled the categories collection to hold main categories in a document and all subcategories within a hierarchical structure.

{
  categoryId: "127642744377643170",
  name: "Schmuck",
  description: "Selber gemachter Schmuck",
  slug: "jewellery",
  categories: [
    {
      categoryId: "644900134728228400",
      name: "Fimo Schmuck",
      description: "Kreative Ideen mit Fimo",
      slug: "fimo",
      categories: [
        {
          categoryId: "149824833816995330",
          name: "Ohrringe",
          description: "Ohrringe aus Fimo",
          slug: "earrings",
          categories: []
        }
      ]
    }
  ]
}

The current structure is practical for managing categories.

But if I want to get a single sub-category, it is not easy because I don’t know how to read out a sub-category directly.

It would be easier if the individual categories were saved in a separate document.

I therefore have the following questions:

  1. What is the best way to model hierarchical structures with Fauna?

  2. If I leave the structure as above, is there a way to be able to query subcategories directly? With a function / index? The query would have to read out the hierarchical structure recursively. What would such a query with fql v10 look like?

Thank you for the support

2

The short answer is: it depends :slight_smile:

Truly, it is a matter of your use case and how you need to query these categories, or if you need to search for other things based on their association with these categories.

One thing I might recommend is to start with a normalized model (separate into separate documents). Later, you might consider duplicating data to optimize read queries, but you would still have the separate documents.

Not efficiently, but you can certainly do it. You would need to query for all categories and compute some filter to check for the children you are looking for.

Category
  .all() // Will scan through the whole collection, so be careful!
  .where(c => {
    // Some lambda or UDF that looks recursively through sub categories.
    checkForCategoryInChildren(c)
  })

Example with normalized data model

As a relational database, Fauna is very capable of working with separate documents with relationships between them. We have some new documentation regarding relationships.

For your category hierarchy, rather than contain an array of sub-categories, each category can contain a reference to a parent category. For example:

{
  id: "365795144543240273",
  coll: Category,
  ts: Time("2023-07-01T00:00:00Z"),
  categoryId: "127642744377643170",
  name: "Schmuck",
  description: "Selber gemachter Schmuck",
  slug: "jewellery",
}

{
  id: "366246628683677776",
  coll: Category,
  ts: Time("2023-07-01T00:00:00Z"),
  categoryId: "644900134728228400",
  name: "Fimo Schmuck",
  description: "Kreative Ideen mit Fimo",
  slug: "fimo",
  parentCategory: Category.byId("365795144543240273")
}

{
  id: "366246628683678800",
  coll: Category,
  ts: Time("2023-07-01T00:00:00Z"),
  categoryId: "149824833816995330",
  name: "Ohrringe",
  description: "Ohrringe aus Fimo",
  slug: "earrings",
  parentCategory: Category.byId("366246628683677776")
}

Every document has it’s own unique id, which is much more efficient to use for relationships than foreign keys, so you see me using those here.

We can query for a category and it’s parent structure:

Category.byId("366246628683678800") {
  id,
  name,
  parentCategory {
    id,
    name,
    parentCategory {
      id,
      name,
    }
  }
}

// result
{
  id: "366246628683678800",
  name: "Ohrringe",
  parentCategory: {
    id: "366246628683677776",
    name: "Fimo Schmuck",
    parentCategory: {
      id: "365795144543240273",
      name: "Schmuck",
    }
  }
}

We can use an index to get all sub categories.

Category.definition.update({
  indexes: {
    subcategories: {
      terms: [{ field: "parentCategory" }]
    }
  }
})

let topCategory = Category.byId("366246628683678800")

topCategory {
  id,
  name,
  categories: Category.subcategories(topCategory) {
    id,
    name
  }
}

I’ve shown these examples with the latest version of FQL, v10. The underlying data model is the same if you are using FQL v4, just the indexes and query language is defined differently.

3

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.