Changing the relationship of "descendants" to the relationship of "brothers and sisters"

Question

I have a variants table as follows:

 +-------------------+------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+------------------+------+-----+---------------------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | parent_product_id | int(10) unsigned | NO | MUL | NULL | | | child_product_id | int(10) unsigned | NO | MUL | NULL | | +-------------------+------------------+------+-----+---------------------+----------------+ 

with restrictions:

 CONSTRAINT `variant_products_child_product_id_foreign` FOREIGN KEY (`child_product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE CONSTRAINT `variant_products_parent_product_id_foreign` FOREIGN KEY (`parent_product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE 

Say it is full:

 | id | parent_product_id | child_product_id | |----+-------------------+------------------| | 28 | 9 | 11 | | 29 | 17 | 30 | | 30 | 9 | 59 | | 31 | 9 | 60 | | 32 | 17 | 25 | 

At first, the business requirements were that a single (parent) product could have several children. In my Product model, I have

 public function variants() { return $this->hasMany(\App\Variant::class, 'parent_product_id', 'id'); } 

And in the Variant model:

 public function child() { return $this->belongsTo(\App\Product::class, 'child_product_id'); } 

When I request Product (id: 9) using:

 $query->with([ 'variants.child' => function ($query) { $query->select(['products.id', 'products.name']) }, ]); 

I get a nice answer:

 { "id": 9, "name": "Foo", "description": "Ipsam minus provident cum accusantium id asperiores.", "variants": [ { "id": 11, "name": "Bar" }, { "id": 59, "name": "Fizz" }, { "id": 60, "name": "Buzz" } ] } 

When you ask about product 59 , there are no options.

Now I need to redefine my relationship so that products and options become more likely brothers and sisters than descendants .

For example, after a request for product 59, the desired answer is:

 { "id": 59, "name": "Fizz", "description": "Lorem ipsum dolor sit amet, consectetur adipisicing elit.", "variants": [ { "id": 9, "name": "Foo" }, { "id": 11, "name": "Bar" }, { "id": 60, "name": "Buzz" } ] } 

How can I achieve this without changing the structure of the database. Any help and advice is greatly appreciated.

Edit : two notes:

• Each child can have only one parent. (There is a unique key child_product_id in the database.)
• There can be only one nesting level. This means that if one product is already a subsidiary, it cannot be a parent for another.

Answer 1

As I said in the commentary, I think it’s better to keep some parents in order to better manage relationships with the sisters. For example, if you want your siblings to be 59, 9, 11, 60, you can save a common parent identifier for everyone, for example 999, which saves them as siblings.

Another thing is that if you have only one parent_product_id for each item, you do not need to store it in a separate table. You can save parent_product_id in the same products table and set options in \App\Product as follows:

 public function variants() { return $this->hasMany(\App\Product::class, 'parent_product_id', 'parent_product_id'); } 

Now you can get a list of brothers and sisters with this small modification in your part of the request:

 $query->with([ 'variants' => function ($query) { $query->select(['products.id', 'products.name']) }, ]);