11
votes

In my database, I have a Categories table. Categories can have parent categories, making it a recursive relationship

I also have a products table. Each product falls under a category.

Say, for example, I have a tree that looks like this:

Category
    Sub-Category 1
        Sub-Sub-Category 1
            Product 1
            Product 2
            Product 3
            Product 4
        Sub-Sub-Category 2
            Product 5
            Product 6
            Product 7
            Product 8
    Sub-Category 2
        Sub-Sub-Category 3
            Product 9
            Product 10
            Product 11
            Product 12

If I do $SubCategory1->products, I want it to give me Products 1-8

If I do $SubSubCategory3->products, I want it to give me products 9-12

If I do $Category->products, I want it to give me all products

Basically, I want the category to give all products that fall under it

8
have you checked Has Many Through? - Cerlin
there are only 2 tables in this example, how would Has Many Through help me? - botmin
Is Store a category too? - Doom5
yes, sorry, should have clarified that - botmin

8 Answers

8
votes

After hoping to find an answer that uses Laravel nicely, I ended up giving up and just writing the code to do what I wanted myself, and it ended up smaller than I anticipated.

public function all_products()
{
    $products = [];
    $categories = [$this];
    while(count($categories) > 0){
        $nextCategories = [];
        foreach ($categories as $category) {
            $products = array_merge($products, $category->products->all());
            $nextCategories = array_merge($nextCategories, $category->children->all());
        }
        $categories = $nextCategories;
    }
    return new Collection($products); //Illuminate\Database\Eloquent\Collection
}
7
votes

Suppose your Model name is Category

Create a function on Category model

public function children() { return $this->hasMany('App\Category', 'parent_id', 'id'); }

Using above method on your controller

$categories = Category::with('children')->where('parent_id',0)->get();
5
votes

This way works very good:

class One extends Model {
    public function children()
    {
        return $this->hasMany(self::class, 'parent_id');
    }

    public function grandchildren()
    {
        return $this->children()->with('grandchildren');
    }
}
3
votes

please try the below Has Many Through relation and post the result

class Category extends Model
{
    public function products()
    {
        return $this->hasManyThrough(
            'App\Product', 'App\Category',
            'parent_id', 'catergory_id', 'id'
        );
    }
}

Then you can use $category->products; to find your products

1
votes

you better use nested sets models. you should use this data structure due to get fastest way to query in mysql with a simple way. with just a parent_id attribute,you don't know how deep your tree is and this make problem in knowing how many join you need. I offer you this excellent article. managing-hierarchical-data-in-mysql

for those who use laravel framework, I prefer to suggest this amazing package:Baum

https://stacklearn.ir

0
votes

i made managed_by in table (users) and this solution get all unlimited levels of children .

in User Model

 public function Childs(){
        return $this->hasMany('App\User', 'managed_by', 'id')->with('Childs');
    }

in helpers file (My magic Solution )

if (!function_exists('user_all_childs_ids')) {
        function user_all_childs_ids(\App\User $user)
        {
            $all_ids = [];
            if ($user->Childs->count() > 0) {
                foreach ($user->Childs as $child) {
                    $all_ids[] = $child->id;
                    $all_ids=array_merge($all_ids,is_array(user_all_childs_ids($child))?user_all_childs_ids($child):[] );
                }
            }
            return $all_ids;
        }
    }
-1
votes

Say $category_id = Category->id and you want a collection of products as children of that category, I would try:

$products = App\Product::with(['SubSubCategory.SubCategory.Category'=>function($query) use ($category_id) {$query->where('id', $category_id);}])->get();

To be able to do so. You will need your 'one to many' inverse relationships to be as such:

//App\SubCategory

public function Category(){return $this->belongsTo('App\Category');}

//App\SubSubCategory

public function Sub_Category(){return $this->belongsTo('App\SubCategory');}

//App\Product

public function SubSubCategory(){return $this->belongsTo('App\SubSubCategory');}

Good luck.

-1
votes

For anyone that is searching for this answer here is a simple solution:

class Group extends Model
{
  public function ancestry() 
  {
   return $this->belongsTo('App\Group', 'parent_id')->with('ancestry'); 
  }

  public function descent() 
  {
    return $this->hasMany('App\Group', 'parent_id')->with('descent'); 
  }
}

ancestry function will give you the path to root and descent will give you all the descendence of that node.