how to write this query in laravel query builder?

January 11, 2022

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

>Solution :

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