What is the correct query for nested relationships ?

@Nakov

My models :

Builders:

 public function project(){
        return $this->hasMany(Projects::class, 'project_builder', 'id');
    }

Projects:

public function builder(){
    	return $this->belongsTo(Builders::class,'project_builder','id');
    }
    public function city(){
    	return $this->belongsTo(Cities::class,'project_city','id');
    }

Cities:

public function project(){
        return $this->hasMany(Projects::class, 'project_city', 'id');
    }

My query is giving me the list of builders with total of all the PROJECTS ( in all the CITIES ) , what I need is the list of BUILDERS with total of PROJECTS in CITIES with status = 1.

@FounderStartup Have you tried this then:

$topbuilders = Builders::whereHas('project.city', function ($query) {
            $query->where('status', 1)->withCount('project');
})->limit(10)->get();

now each of your project cities will contain their total of projects.

I really don't like the way you are naming things as it is confusing. A builder has many projects but you name the relationship as project as if there is one, same for the opposite. Seems like a project doesn't have one builder, which makes it even more confusing.

@Nakov Bro this code I am already using :). This code is giving me list of builders with total of all the PROJECTS ( in all the CITIES ) , what I need is the list of BUILDERS with total of PROJECTS in CITIES with status = 1.

I just check and found this code in CITIES model

   public function scopeEnabled($query)
    {
        return $query->where('status', 1);
    }

is it causing the issue ?

Yes I need to improve on the naming system as it has caused lot of problems on the production server also :).

@FounderStartup nope, that is not causing issue that's a replacement instead of writing this $query->where('status', 1) you can write $query->enabled()

and btw, the code I shared above is not what you are already using :) check where the withCount() is for me, and where it is for you. The one I showed you uses PER City count as you asked.

@Nakov I don't want total per CITY. I need to show the BUILDERS with total projects in CITIES which have status = 1. So its not per city but for around 10 cities which have status = 1. Presently my query is showing BUILDERS with total projects in all CITIES ( around 250 ).

@FounderStartup oh okay, got it. Try this one out then:

Builders::withCount(['project' => function($query){
		$query->whereHas('city', function ($query) {
			$query->where('status', 1);
		});	
	})
	->orderByDesc('project_count')->limit(10)->get();;

Something like this then?

@Nakov

ParseError syntax error, unexpected ')', expecting ']'

@FounderStartup sorry forgot the bracket:

Builders::withCount(['project' => function($query){
		$query->whereHas('city', function ($query) {
			$query->where('status', 1);
		});	
	}])
	->orderByDesc('project_count')->limit(10)->get();

@Nakov Brilliant work !!! You are amazing !!! Thanks a ton :)

@FounderStartup "Best Answer" ? :)

@Nakov Its done Chief :)