Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

eusapxai's avatar

get category and their products

categories table 
    |->cat_id
    |->cat_name

products  table 
    |->prd_id
    |->cat_id

featured tables
    |->id
    |->prd_id
    |->cat_id

i have 3 categories which has many relation and 4th one has relation to featured and the featured has relation to products.

so what i want is to get all the categories and in that category i want the products of that category. so far the products of first 3 category is simple to get but for the 4th one i am getting an empty array.

my controller function

Category::with(['products', 'products.avgRating', 'products.totalReviews'])
                            ->where([['status', '=', 0]])
                            ->get();

my modal

in category model
    public function products()
    {
        return $this->hasMany(Product::class, 'cat_id', 'cat_id')->where([['quantity', '>', '0']]);
    }

in product model
public function category()
    {
        return $this->belongsTo(Category::class, 'cat_id', 'cat_id');
    }

    public function featured()
    {
        return $this->hasOne(Featured::class,'product_id', 'prd_id');
    }

    public function avgRating()
    {
        return $this->ratings()
            ->selectRaw('avg(stars) as avgrating, prd_id')
            ->groupBy('prd_id');
    }

    public function totalReviews()
    {
        return $this->ratings()
            ->selectRaw('count(DISTINCT rating_id) as reviews, prd_id')
            ->groupBy('prd_id');
    }

and my output is

[
{
"cat_id": 1,
"en_name": "Vegetables",
"description": "Vegetable Decription",
"products": [], //20 items
"status": 0,
"cat_order": 3
},
{
"cat_id": 2,
"en_name": "Fruits",
"ur_name": "Phal",
"description": "Description of Fruits",
"products": [], // 30 items
"status": 0,
"cat_order": 2
},
{
"cat_id": 6,
"en_name": "Farm To Home Special",
"ur_name": "Farm To Home Special",
"description": "Description of Farm To Home Special",
"products": [], //10 items
"status": 0,
"cat_order": 4
},
{
"cat_id": 7,
"en_name": "Top Products",
"description": "Description of Top Products",
"products": [], //null
"status": 0,
"cat_order": 1
}
]

In the response of last array the products array is empty and when i add join to the featured table like this

in my category model
public function products()
    {
        return $this->hasMany(Product::class, 'cat_id', 'cat_id')->join('featured_products', 'featured_products.product_id', '=', 'products.prd_id')->where([['quantity', '>', '0']]);
    }

and changing this i get the products for the last array but the first 3 array products are empty.

0 likes
4 replies
Snapey's avatar

Do you have any products in category 7 ?

eusapxai's avatar

@snapey the category 7 products in featured table which is linked with the products tables.

It may sounds confusing but the category 1 2 and 6 has direct relation with products table but category 7 is in relation with featured table and the featured table has direct relation to products table.

Snapey's avatar

Can't see how that will ever work?

Cronix's avatar
Cronix
Best Answer
Level 67

It may sounds confusing but the category 1 2 and 6 has direct relation with products table but category 7 is in relation with featured table and the featured table has direct relation to products table.

That sounds like it should be a polymorphic relationship so everything is stored in a single table. Or the products table should just have a featured flag (0/1) to indicate that it's a featured product instead of a separate table. What is the difference between a product, and a featured product in terms of the database?

Please or to participate in this conversation.