belongsToMany with parent-child relationship in Laravel 5

Question

I have two models that are related to each other with a belongsToMany relationship. The models are Game and Category. Of course, the tables for both are games and categories. The Category model, has its own parent-child relationship.

Basically this is my "simplified" structure:

Table game:
id          unsigned integer
name        string

Table categories:
id          unsigned integer
name        string
parent_id   unsigned integer nullable

The parent_id column is null when the category has no parent, but it has an existing id referencing a row in the same table if it is a children of some other category.

Table category_game
category_id unsigned integer
game_id     unsigned integer

The category_id column, references id on categories table. It should reference only the top category that a game belongs to. A game can belong to many different categories, but in the pivot table, there should only be a reference to the parents categories. For example if I had this structure of categories:

Category 1
  Category 2
    Category 4
  Category 3
    Category 9
Category 5
  Category 6
  Category 7
    Category 8

I would like to have the following information for my games 1 and 2:

category_id  game_id
          3        1
          5        1
          1        2

And that should mean that my game 1 has categories: 3, 9, 5, 6, 7 and 8. While my game 2 has categories: 1, 2, 4, 3 and 9

I understand that my Laravel models should have this:

class Game {
    public function categories(){
        return $this->belongsToMany( Category::class );
    }
}

class Category{
    public function games(){
        return $this->belongsToMany( Game::class );
    }
}

But I don't know how to retrieve the children categories using Eloquent. I know the belongsToMany method has more parameters that might help with my problem, but I don't know how to use them.

Answer 1

Extend your models:

class Category {
    public function children() {
        return $this->hasMany(Category::class, 'parent_id');
    }
}

class Game {
    public function getAllCategoriesAttribute() {
        $result = collect();
        $children = function($categories) use(&$result, &$children) {
            if($categories->isEmpty()) return;
            $result = $result->merge($categories);
            $children($categories->pluck('children')->collapse());
        };
        $children($this->categories);
        return $result;
    }
}

Then you can access the categories like this:

Game::find($id)->allCategories;