Multiple category id wise show product in laravel 9

Question

I am trying to building ecoommerce system using laravel 9. Everything goes fine but Suddenly I got one issue. I want to show product based on category id but my product table category id store multiple. Bellow I am showing my table structure:

Product Table:

|---ID---|---Name---|---Cat_id---|---Status--|
|   1    | T-shirts |    1,2,    |   active  |
|   2    | Pants    |    4,3,    |   active  |
|   3    | Sweaters |    5,2,    |   active  |

Category Table

|---ID---|---Name---|
|   1    | General  |
|   2    | News     |
|   3    | Festival |
|   4    | Category |

Controller

public function category($slug)
{
    //
    $cat = Category::where('slug', $slug)->first();

    $products = Product::whereIn('cat_id', [$cat->id])->where('status', 'active')->orderby('id', 'asc')->paginate('12');

    return view('frontend/Catproducts', compact('products', 'cat'));
}

Now I want when I am click on "NEWS" category I want to see two product. How can I fix it

Answer 1

As @aynber said in the comments, best solution is to normalize the database, so you have a setup like this:

Categories table:

• id
• name

Products table:

• id
• name
• status

Product_categories table:

• id
• product_id
• category_id

With a belongsToMany relationship from Product to Category(and back), your query should look something like:

$categoryIdArray = [2];
Product
    ::whereHas('category', function($query) use($categoryIdArray) {
        $query->whereIn('id', $categoryIdArray);
    })
    ->get();

It is still possible with your current setup though, while a little hacky:

$cat = Category
    ::where('slug', $slug)
    ->first();
$catId = $cat->id;
$products = Product
    ::where(function($where) {
        $where->where('cat_id', 'like', "$catId,%")//Look for first cat in col, don't forget trailing comma
            ->orWhere('cat_id', 'like', "%,$catId,%")//Look for appended cat between commas
    })
    ->where('status', 'active')
    ->orderby('id', 'asc')
    ->paginate('12');

This will work on small scale, but because we are using like, mysql needs to check ALL records, and cannot optimize the query. That's the reason why normalization is important :)