how to write this query in laravel query builder?

Question

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

AntG · Accepted Answer · 2022-01-11 15:15:43Z

2

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

answered Jan 11, 2022 at 15:15

AntG

1,2962 gold badges20 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

herdin jaya Over a year ago

thanks for the response, that work well with small edited like this in my. "$result = DB::table('cities') ->join('patiens', 'cities.id', '=', 'patiens.cityidp') ->select(DB::raw('cities.cityname, count(distinct patiens.namep) as statecount')) ->groupBy('cities.cityname') ->orderBy('statecount', 'desc') ->get();"

Seyid Takele Over a year ago

To alias table name you may AS keyword.DB::table('cities AS c')->get(); => select * from cities as c

Autista_z · Accepted Answer · 2022-01-11 15:30:25Z

0

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/>";
}

answered Jan 11, 2022 at 15:30

Autista_z

2,53118 silver badges25 bronze badges

1 Comment

herdin jaya Over a year ago

@AntG already answer my question, thanks a lot and you too

Collectives™ on Stack Overflow

how to write this query in laravel query builder?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related