how to write this query in laravel query builder?-CodePudding

im new in laravel world n didn't know well how to write proper code. i have a query

SELECT c.cityname, count(distinct s.namep) as statecount FROM cities c INNER JOIN patiens s on c.id = s.cityidp GROUP BY c.cityname ORDER BY statecount DESC;

output query like this

in my sql database, its work really well with output like i want, but the problem i can't implemented that query in laravel.

in that query i want get how many every city already used by patiens. thanks a lot for help before

CodePudding user response：

Because of the count, you will probably need to use an element of Raw expression Laravel QueryBuilder

$result = DB::table('cities c')
             ->join('patiens s','c.id','=','s.cityidp')
             ->select(DB::raw('c.cityname, count(distinct s.namep) as statecount'))
             ->groupBy('c.cityname')
             ->orderBy('statecount','desc')
             ->get();

CodePudding user response：

Ideal would be, if you had tables as Models (City, Patien) and Relationships between this two Models.

//City model
public function patiens(){
    return $this->hasMany(Patien::class, 'cityidp');
}

Than you could just do:

$cities = City::withCount('patiens')->get();

And in result loop:

foreach($cities as $city){
    echo $city->cityname." -> ".$city->patiens_count."<br/>";
}