Change 'descendants' relationship to 'siblings' relationship

Question

I have variants table as following:

+-------------------+------------------+------+-----+---------------------+----------------+
| 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 constraints:

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

Let's say that it is filled with:

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

At first, business requirements was that one (parent) product can have multiple children. In my Product model I have

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

And in Variant model:

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

When I am querying Product (id:9) using:

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

I am getting nice response:

{
  "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 asking about product 59, there are not any variants.

Now, I need to redefine my relationships so that products and variants will become rather siblings than descendants.

For example, after asking about product 59, desired response 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 it without changing database structure. Any help and tips are much appreciated.

Edit: Two notes:

• Every child can have only one parent. (There is a unique key on child_product_id column in database.)
• There can only be one level of "nesting". Which means, that if one product already is a child - it cannot be a parent to another.

Answer 1

As I said in the comment, I think it is still better to keep some type of parent to better manage the sibling relationships. For example if you want 59, 9, 11, 60 to be siblings, you can keep a common parent id for them all, like 999, which will keep them as siblings.

The other thing is that, if you have only one parent_product_id for each item, you don't need to keep it in a separate table. You can keep the parent_product_id in the same table products and set variants in \App\Product like this:

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

Now, you can get the list of siblings with this little modification to your query part :

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