How to query a Hierarchical Category tree entity with undefined depth

I have created a hierachy structure or a tree structure for a shopping site using springboot. My problem is how to query such a structure when looking for a specific product and its parents:

id,  category_name,   parent_id
'1', 'Electronics',      NULL
'2', 'Gaming',           NULL
'3', 'Home Audio',       '1'
'4', 'Console',          '2'
'5', 'Sony',             '4'
'6', 'Karaoke',          '3'

This is what i have done, any pointers on what i need to do on the entity to achieve this structure and also how i can query it i.e

It is also important to note that i am using postgres database

1. findAllProducts in a category and
2. find all categories linked to a product.

Category Entity

@Entity
@Table(name = "categories")
public class Category {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(nullable = false)
    private String categoryName;

        @ManyToOne
        @JoinColumn(name = "parent_id")
        private Category parent;

        @OneToMany(mappedBy = "parent", cascade = CascadeType.REMOVE, orphanRemoval = true)
        private List<Category> children = new ArrayList<Category>();

    // Getter and setter removed for readability    
}

Product Entity

@Entity
@Table(name = "products")
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private Long id;

    @Version
    @Column(name = "version")
    private Integer version;

    private String name;

    private int quantity;

    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "products_categories", joinColumns = {
            @JoinColumn(name = "product_id", referencedColumnName = "id") }, inverseJoinColumns = {
                    @JoinColumn(name = "category_id", referencedColumnName = "id") })
    private List<Category> categories;

  // getters and setter omitted for readability

}

Category Service

public class CategoryService {

@Autowired
private CategoryRepository categoryRepository;

public void addCategory(Category category) {
    categoryRepository.save(category);
}
}

Product Service

public class ProductService {

    @Autowired
    private ProductRepository productRepository;

    public void addProduct(Product product) {
        productRepository.save(product);
    }
   }

Answer

You can use a recursive query to select the parents of a given category.

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface CategoryRepository extends JpaRepository<Category, Long> {

  @Query(
      value = "WITH RECURSIVE ancestors(id, parent_id, category_name, lvl) AS ("
          + "   SELECT cat.id, cat.parent_id, cat.category_name, 1 AS lvl "
          + "   FROM categories cat "
          + "   WHERE cat.id = :categoryId "
          + "   UNION ALL "
          + "   SELECT parent.id, parent.parent_id, parent.category_name, child.lvl + 1 AS lvl "
          + "   FROM categories parent "
          + "   JOIN ancestors child "
          + "   ON parent.id = child.parent_id "
          + " )"
          + "SELECT category_name from ancestors ORDER BY lvl DESC"
      , nativeQuery = true)
  List<String> findAncestry(@Param("categoryId") Long categoryId);
}

Following is a test that creates the category hierarchy and queries it:

import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;

@RunWith(SpringRunner.class)
@SpringBootTest
public class HierarchyTest {

  @Autowired
  CategoryRepository categoryRepository;

  @Test
  public void testCategoryRepository() {
    String[] electronicsCategoryNames = {"Electronics", "Home Audio", "Karaoke"};
    String[] gamingCategoryNames = {"Gaming", "Console", "Sony"};

    Category karaoke = createCategories(electronicsCategoryNames);
    Category sony = createCategories(gamingCategoryNames);

    findAncestry(karaoke, electronicsCategoryNames);
    findAncestry(sony, gamingCategoryNames);
  }

  Category createCategories(String[] categoryNames) {
    Category parent = null;

    for (String categoryName : categoryNames) {
      Category category = new Category();
      category.setCategoryName(categoryName);
      category.setParent(parent);
      parent = categoryRepository.save(category);
    }

    Assert.assertNotNull("category.id", parent.getId());
    return parent;
  }


  void findAncestry(Category category, String[] categoryNames) {
    List<String> ancestry = categoryRepository.findAncestry(category.getId());
    Assert.assertEquals("ancestry.size", categoryNames.length, ancestry.size());

    for (int i = 0; i < categoryNames.length; i++) {
      Assert.assertEquals("ancestor " + i, categoryNames[i], ancestry.get(i));
    }
  }
}

Note that this was tested on H2 and PostgreSQL databases. You might need to modify the native query according to the actual database you are using.

PostgreSQL has excellent documentation on how this works, see:

https://www.postgresql.org/docs/11/queries-with.html