Laravel: Products,Categories and SubCategories! (Relation ships)

Question

I have a problem! I make something but, not work so i will ask u for help! So the problem is

I have this tables

products:
  name,subcategory_id,category_id,price,description
Categories:
  name,slug,timestamps
SubCategories:
  name,slug,timestamps

I want to make when smo call this url /category/{category}/{subcategory} to get all products of subcategory are called! But when product don't have sub category to open only category , i mean /category/{category}

Thanks guys!

Answer 1

Here i may help u with this!

First , you can use only one table for categories and subcategory!

You can make this:

 |---------------------------------------
 |id| |parent_id|    |name|       |slug|
 |1 | | (NULL)  | Electronics | electronics
 |2 | | 1       | Phones      | phones

Now category electronic have children Phones

So in your Category.php model u can make that

/**
* @return \Illuminate\Database\Eloquent\Relations\BelongsTo
*/
public function parent() {
    return $this->belongsTo(self::class, 'parent_id');
}

/**
 * @return \Illuminate\Database\Eloquent\Relations\HasMany
 */
public function children() {
    return $this->hasMany(self::class, 'parent_id','id');
}

Now u can use foreach to display your children and category! Hope that helpful! ;)

Answer 2

It's classic problem related with databases You should use hierarchical child/parent relation. At first Your design of database is bad, just add one column with root category which has "0" in PARENT_ID.

CREATE TABLE CATEGORIES ( 
      CATEGORY_ID  NUMBER,
      PARENT_ID    NUMBER,
      NAME         VARCHAR(255), 
      CREATE_TS    TIMESTAMP(0));

and then read this : http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ & How to create a MySQL hierarchical recursive query & Bear in mind that You are mixing logic of GUI with SQL role. If You want to return in single query then Your presentation layer will be quite ugly ;-)

and also think about leaving just category_id in product table, if You have only 2 or 3 levels You don't need to point to subcategory and category.