Home / Questions / 441633
Accepted
René Merlo
Asked: 2022-04-05 18:46:52 +0800 CST 2022-04-05 18:46:52 +0800 CST

How to return a sum of certain fields from a table via Eloquent?

  • 772

I have a table with a series of numeric columns and I need to return via Eloquent a column that is the sum of three fields of the same table, then I show you the structure of the table and what I need to return.

enter image description here

How should I build the query via Eloquent so that it returns all the records but also generates a column with the sum of the columns C1_01, C1_02 and C1_03 called totPartial, for example?

    $tbSnej1 = Snejtabsnej1::paginate(5);

    $totParcial = 0;
    foreach ($tbSnej1 as $it){
        $totParcial = ($it->C1_01 +$it->C1_02 +$it->C1_03 );
        //aquí debería armar el array con todos los campos mas $totParcial
        //¿como debería armar el array para devolverlo en el return?
    }

This is what I was trying to do, i.e. retrieve the records and then using a foreach loop through and build the summed field I need. Now, I'm stuck on how to build an associative array to return this structure as Eloquent itself would return it.

laravel

1 Answers

  1. Best Answer

    I believe that from SQL you can solve the need raised, carrying out the following:

    Snejtabsnej1::selectRaw('id, 
                        anioRelev, 
                        provincia_id, 
                        SUM(C1_01 + C1_02 + C1_03) AS totParcilal,
                        aqui_tus_demás_columnas_separadas_por_comas
                        ')->groupBy(
                            'id',
                            'anioRelev',
                            'aqui_tus_demas_columnas_no_calculadas_separadas_por_coma'
                        )->get();
    • With the help of the aggregation functionSUM we obtain the calculated value in a new column to which we can give an alias
    • As in addition to the sum you need the rest of the columns, you can include the rest in the request through the methodselectRaw
    • Finally you make a grouping
    • 2