0

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.

Tales
  • 1,829
  • 3
  • 33
  • 50

2 Answers2

1

Model categoryGame for table category_game

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

You can access

$games = App\CategoryGame::all();

foreach ($games as $game ) {
    foreach ($game->childCategories as $category) {
      echo $category->name;
     }
}

Let me know if not works

afsal c
  • 612
  • 4
  • 12
  • I tested your approach and it works, but I went with the first answer because for your approach I had to create a new model. – Tales Apr 09 '18 at 14:02
0

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;
Jonas Staudenmeir
  • 24,815
  • 6
  • 63
  • 109
  • I used your code and I got this error message: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'categories.category_id' in 'where clause' (SQL: select * from `categories` where `categories`.`category_id` = 1 and `categories`.`category_id` is not null) – Tales Apr 06 '18 at 20:38
  • 1
    Was your comment meant for the other answer? – Jonas Staudenmeir Apr 06 '18 at 21:33
  • No sir, it was for your answer. When I used your code, I got the error message that I posted. – Tales Apr 07 '18 at 00:58
  • Turns out you were right. I was missing the foreign key `parent_id` in the Parent-Child relationship, so it was looking for a column called `category_id`. Thanks for your help. – Tales Apr 09 '18 at 14:00