title: Spring Boot - searching category tree
body: <p>I am writing Spring Boot application which is basically advertisement service. In database (I use PostgreSQL dialect) I store tree of categories. Each category holds a reference to its immediate parent. Each advertisement has his category assigned and for the sake of the problem, you can assign an advertisement only to leaf category.</p>
  
  <p>An image below captures the structure.</p>
  
  <p><a href="https://i.stack.imgur.com/6eVBy.png" rel="nofollow noreferrer">Category tree</a></p>
  
  <p>Now I need to retrieve advertisements of specific category from database. But let's say I want to retrieve all advertisements of category E. That means I want to retrieve advertisements of categories which are children of category E, but not necessarily immediate children. In this case I would like to retrieve advertisements of categories G, H, I and J because they are all leafs and they are all children of category E.</p>
  
  <p>How can I achieve it via Spring repository? Here are simplified entity classes for this example.</p>
  
  <p>Category...</p>
  
  <pre><code>@Entity
  @Table(name = "categories")
  public class Category {
  
      @Id
      @Column(name = "cat_id")
      private int id;
  
      @Column(name = "cat_name", length = 50, nullable = false, unique = true)
      private String name;
  
      @ManyToOne(cascade = CascadeType.ALL)
      @JoinColumn(name = "cat_parent", referencedColumnName = "cat_id", nullable = false)
      private Category parent;
  
      ...
  }
  </code></pre>
  
  <p>... and Advertisement.</p>
  
  <pre><code>@Entity
  @Table(name = "advertisements")
  public class Advertisement {
  
      @Id
      @Column(name = "adv_id")
      private long id;
  
      @Column(name = "adv_title", length = 100, nullable = false)
      private String title;
  
      @Column(name = "adv_description", length = 5000, nullable = false)
      private String description;
  
      @ManyToOne(cascade = CascadeType.ALL)
      @JoinColumn(name = "adv_category", referencedColumnName = "cat_id", nullable = false)
      private Category category;
  
      @ManyToOne(cascade = CascadeType.ALL)
      @JoinColumn(name = "adv_owner", referencedColumnName = "user_id", nullable = false)
      private User owner;
  
      ...
  }
  </code></pre>