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

When i try to get approved news for any category i get all items return i m doing like this $items=Category::with('subcategory')->where('id',1)->get(); and in inverse its not getting categories or main section — Rao, Sep 06 '19 at 02:25
Can you update the question with a concrete code sample of what you are trying to do and what you get instead? — Olivenbaum, Sep 06 '19 at 07:07
@Rao When you are doing `$items=Category::with('subcategory')->where('id',1)->get();` are you trying to get the category with the id of 1, or categories where the subcategory is 1? — party-ring, Sep 06 '19 at 09:44

Olivenbaum · Accepted Answer · 2019-09-10T23:20:09.290

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);

score 0 · Answer 2 · answered Sep 06 '19 at 09:53

I have a few suggestions of how you can get this to work. In your comments, you say you are getting an issue doing the following:

$items=Category::with('subcategory')->where('id',1)->get();

Where is 'subcategory' coming from? By the looks of your model, your relationship between Category and Subcategory is called subcats. So you would need to change it to:

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

And if you dump that out, you should see that you will get the category where the ID is 1, and the subcategories loaded in. A way to test that your relationships are working would be something like this:

$category = Category::find(1);
$subCats = $category->subcats()->get();
dd($subCats);

In your relationships, instead of using SubCategory::class I would suggest trying return $this->belongsToMany('App\SubCategory'); so that the models are definitely connected.

Once you have tested that your relationships between one another work, you can get started on testing that you can go from a->b->c etc.

score 0 · Answer 3 · answered Sep 09 '19 at 13:12

May be using "Nested Eager Loading" and "scope", you can do something like

$pendings = NewSection::with('categories.subCategories')->pending()->get()
$approved = NewSection::with('categories.subCategories')->approved()->get()

not tested it, but you can try, may be with some modification, you can reach to your goal.

if you want return one collection, you may like to merge it

$approved->merge($pendings);

but, you should avoid it.

Laravel, many-to-many relationship among multiple models

3 Answers3