Laravel, many-to-many relationship among multiple models

Question

I Have multiple models that have many to many relationship

Here are the models

• News Section
• Categories
• Sub Categories
• Approved News
• Pending News

Each News Section can have multiple Categories.

Each Category can have multiple Sub Categories.

Each Sub Category can has multiple Approved News and Pending News.

I want to have News with Categories, Sub Categories and Pending / Approve news

and stuff like

Categories with Sub Categories and Approve news

I tried with pivot tables but not able to get results

Models are as follow

News Section

class NewsSection extends Model
{
     public function categories()
    {
    return $this->belongsToMany(Category::class);
    }
}

Category

class Category extends Model
{
    public function subcats(){
       return $this->belongsToMany(SubCategory::class);
    }
    public function newssections(){
      return $this->belongsToMany(NewsSection::class);
    }
}

SubCategory

class SubCategory extends Model
{
    public function category(){
     return $this->belongsTo(Category::class);
    }

    public function approvednews(){
     return $this->belongsToMany(ApprovedNews::class);
    }

   public function pendingnews(){
     return $this->belongsToMany(PendingNews::class);
   }

}

ApprovedNews

class ApprovedNews extends Model
{

    public function subcategories (){
     return $this->belongsToMany(SubCategory::class);
   }
}

PendingdNews

class PendingdNewsextends Model
{

    public function subcategories (){
     return $this->belongsToMany(SubCategory::class);
    }
}

Update This what I have done so far

$news =Category::with('subcats.approvednews')->where('id',1)->get();

I got all the approved news with subcategories and categories

how can i modify this to get specific subcats and approved news per category, if i do this

$news =Category::with('subcats.approvednews')->where('subcats.id',1)->get();

I get an error like id ambiguous

Is it possible to pick and chose items from relation for instance return just 2 subcats and 3 approved news for each subcat of selected category

or

get count of approved news and pending news per subcat and category

Thanks in advance

Answer 1

The error "error like id ambiguous" means that you need to specify the table in your where('id', 1) like where('table.id', 1) so that MySQL knows which id column in which table you mean.

You can constrain the models returned by with like this:

Category::with(['subcats' => function(Builder $query) {
    $query->where('id', '=', 1);
}]);

Also you can count relations:

$subcat = SubCategory::withCount(['approvednews']);
$subcat->approvednews_count;

Limiting eager loaded relations is not possible per the docs.

A workaround may be to go the other way round starting from ApprovedNews:

ApprovedNews::whereHas(['subcategories' => function(Builder $query) {
    $query->where('id', '=', 1);
}])->limit(10);