I have a model/table Categories that have a Foreign Key to itself.

I have a model/table Products. Each Product can have multiple categories and each category can have multiple products.

Category Table

    id | name | parent_id
    
    1    name1   null
    2    name2   1
    3    name3   2 
    4    name4   1
    5    name5   4
    6    name6   null

Cat-Prod Relation Table

    id | name | product_id | category_id
    
    1    name1   1            1
    2    name1   1            2
    3    name2   2            1
    4    name3   3            1
    5    name4   4            2
    6    name5   5            2
    7    name6   6            3

Products Table

    id | name 
    
    1    name1   
    2    name2   
    3    name3   
    4    name4   
    5    name5   

If I want to get all products that 'are' in category with id 1 (including subcategories) I need to get products from 1 and 2, 3, 4, 5 because they are children, grand_children of 1.

If I link/add a product to a category (ex:5), what is the best approach?

1) every time I add a product to a subcategory add it also to the parent, grandparent etc

ex- add it also in 4 and 1 - this adds a lot of rows in database for the product category relation, but needs one query to retrieve products.

2) add link just to 5 and use recursive

just one row in database, but many calls to a db at retrieval.

3) another approach

I need also to take in consideration that a Product can be in multiple categories.