How to create an Laravel Eloquent query that return more than one aggregate?

user_11754455 0
Viewed 604

I am a Laravel and Eloquent newbie.

I'm trying to port an old application to Laravel.

Right now I would like to convert the following RAW SQL query to Eloquent:

  SELECT COUNT(county_id) AS qt_counties,
      SUM(population) AS total_population
    FROM population_county
    WHERE population >= 5000
      AND population < 10000
      AND year = "2020"
    GROUP BY year

The problem I'm facing is that I can't figure out how to write an Eloquent query that return two aggregates at the same time.

I tried

  $qt = PopulationCounty::where('year', $year)
    ->where('population', '>=', $min)
    ->where('population', '<', $max)
    ->distinct('county_id')
    ->sum('population');

but it only returns one of the aggregates.

Can it be done as a Eloquent query or should I use some kind of "RAW query" support in Eloquent?

eloquent laravel php
2 Answers

Looks like you can use a SelectRaw to return multiple aggregates:

This might be helpful:

https://laravel.io/forum/06-17-2014-multiple-aggregates-in-one-query

The reply from jarektkaczyk should help you be on your way.

Hes basically saying that you can build up the Query in a more RAW state, rather than trying to bend eloquent into doing what you want :)

user_25515910 
$qt = PopulationCounty::selectRaw('COUNT(county_id) AS qt_counties, SUM(population) AS total_population')
        ->where('year', $year)
        ->where('population', '>=', $min)
        ->where('population', '<', $max)
        ->groupBy('year');
user_9877040
Related