1

We are trying to build an application in Laravel 8 where we need to fetch some data through complex queries. Let me explain my model structure to you:

I have a Brand model, which is related to multiple Group and SubGroup model with many-to-many relation. Each brand is associated with multiple Project with respect to Group and SubGroup model.

So my Brand model looks like:

public function group()
{
    return $this->belongsToMany(Group::class, 'brand_group', 'brand_id', 'group_id');
}

public function subgroup()
{
    return $this->belongsToMany(SubGroup::class, 'brand_subgroup', 'brand_id', 'sub_group_id');
}

public function projects()
{
    return $this->belongsToMany(Project::class, 'project_associate_brand', 'brand_id', 'project_id');
}

I've a pivot table project_associated_brand which has project_id, group_id, sub_group_id, brand_id Considering the pivot table I've defined the relationship in Project, Group and SubGroup:

Project model:

public function brand()
{
    return $this->belongsToMany(Brand::class, 'project_associate_brand', 'project_id', 'brand_id');
}

public function group()
{
    return $this->belongsToMany(Group::class, 'project_associate_brand', 'project_id', 'group_id');
}

public function subgroup()
{
    return $this->belongsToMany(SubGroup::class, 'project_associate_brand', 'project_id', 'sub_group_id');
}

Group model:

public function projects()
{
    return $this->belongsToMany(Project::class, 'project_associate_brand', 'group_id', 'project_id');
}

public function brand()
{
    return $this->belongsToMany(Brand::class,'brand_group',
        'group_id', 'brand_id')->withTimestamps();;
}

SubGroup model:

public function projects()
{
    return $this->belongsToMany(Project::class, 'project_associate_brand', 'sub_group_id', 'project_id');
}

public function brand()
{
    return $this->belongsToMany(Brand::class,'brand_subgroup',
        'sub_group_id', 'brand_id')->withTimestamps();;
}

I want to extract Project count from SubGroup with respect to each Brand

return SubGroup::with(['group', 'brand.projects' => function($q) {
            $q->where('projects.status','saved');
        }])->get();

Currently I'm getting the output:

[ {sub_group_name: 'abc subgroup', brands: [ {brand_name: 'lddf', project_count: 10}, {brand_name: 'uoweyr', project_count: 21}]} ]

Currently I'm getting all the projects count related to brands. I need to filter them too with respective SubGroup models also, what I expect the output:

[ {sub_group_name: 'abc subgroup', brands: [ {brand_name: 'lddf', project_count: 8}, {brand_name: 'uoweyr', project_count: 16}]} ]

Here project_count is filtered as per the Brand and SubGroup

shaedrich
  • 5,457
  • 3
  • 26
  • 42
Ruchira
  • 75
  • 4

2 Answers2

0

You need to pass the where callback function to both the relation and the parent query:

$fn = function($q) {
    $q->where('projects.status','saved');
};
SubGroup::with(['group', 'brand.projects' => $fn])->whereHas('brand.projects', $fn)->get();
shaedrich
  • 5,457
  • 3
  • 26
  • 42
0

you can use where Has call back function , this method allow you to write a query as Project model

read more from here : [1]: https://laravel.com/docs/8.x/eloquent-relationships

zanko
  • 41
  • 1
  • 5