Laravel - Get data by Month and sum it (varchar)

Question

I´ve seen similar questions but honestly, I didn´t see anything that could take me to the right way and that´s because I´m also looking for a better way of doing this.

At certain point in my app i´m using chartjs, where i need to show months and then the values(sales). For that i know(or i think) i have to retrieve data group by months, and i got here so far:

$records = TabelaAngariacao::select('preco', 'created_at')
    ->where('estado', '=', 'Vendido')
    ->get()
    ->groupBy(function($date) {
       return Carbon::parse($date->created_at)->format('m');
    });

Well...as you can see, I´m not SUMING anything, and that´s because in my database my values are getting stored in varchar(with dot´s!) and I know that´s not the proper way of doing things, but at this point changing everything it´s not a solution for me right now, so how could i change that function in order to remove the dot´s and do a proper sum? I have tried using CAST but it´s giving me undefined function.

Any suggestions? Thanks for your time, regards.

--EDIT--

Hey everyone, i think i´m almost there, i didn´t know about the transform function, in there i can make some modifications on the preco(price) field which still has the problem of being a varchar, so i need to remove the dot´s in order for the sum to work, what i have is:

$records = TabelaAngariacao::select('preco', 'created_at')
            ->where('estado', '=', 'Vendido')
            ->get()
            ->groupBy(function($date) {
                return Carbon::parse($date->created_at)->format('m');
            })->transform(function ($value) { //it can also be map()
                $nStr = str_replace(".", "", $value->pluck('preco'));
                return [
                    'preco_sum' => $nStr->sum()
                ];
            });

but this does not work, because $nStr is string...what do i need to do? if i convert to float, then the sum will not work...if i leave this way, the sum between 175.000 and 1.000.000 will be 176.000 which is wrong...any thoughts?

Answer 1

try this query .

$records = TabelaAngariacao::select(
   \DB::raw('sum(cast(preco as double precision)) as sums'), 
   \DB::raw("DATE_FORMAT(created_at,'%M %Y') as months"))
->where('estado', '=', 'Vendido')
->groupby('months')->get();

Answer 2

Friend, if you want to make use of collection to group and also sum the values in group I believe its not too late.

Let's use the example below:

I'll make a sample result of your eloquent query with the following... Say we have users

    $users = collect();
    foreach (range(1, 9) as $item) {
        foreach (range(1, 9) as $ninja) {
            $users->push([
                'created_at' => \carbon('2018-0' . $item),
                'preco' => (string)'160.00' . $item
            ]);
        }
    }

The above should have something like this...

[
  {
    "created_at": "2018-01-01 00:00:00",
    "preco": "160.001"
  },
  {
    "created_at": "2018-01-01 00:00:00",
    "preco": "160.001"
  },
  {
    "created_at": "2018-01-01 00:00:00",
    "preco": "160.001"
  },
...
]

We then group by, and transform (or map) the result using...

   $users->map(function ($user) {
        $user['preco'] = str_replace('.', '', $user['preco']);
        return $user;
    })->groupBy(function ($user) {  
        return Carbon::parse($user['created_at'])->format('m');
    })->transform(function ($value, $key) { //it can also be map()
        return [
            'preco_sum' => $value->pluck('preco')->sum()
        ];
    });

Here we have the sum of each of the group of months...

UPDATE

If the record is big, then you can cut down the mapping by using Accessor

You could have something as follows that formats your 'preco' field in your model like so:

/**
 * Get the user's first name.
 *
 * @param  string  $value
 * @return string
 */
public function getPrecoAttribute($value)
{
    return str_replace('.', '', $value);
}

This means when Laravel is building the Eloquent objects, this value would be formatted as specified which means you can remove the first map() from the result.

PS: you might need a mutator to change it to the format you want it in the database when saving.

Cheers!

Answer 3

I'm doing this to totalize and group by months

$ventas = Operacion::select([
            DB::raw("DATE_FORMAT(created_at, '%Y') year"),
            DB::raw("DATE_FORMAT(created_at, '%m') month"),
            DB::raw("SUM(1) cantidad"),
            DB::raw("SUM(operacion_total) total"),
            DB::raw("SUM(CASE WHEN estado = 'OPEFIN' THEN operacion_total ELSE 0 END) total_finalizado")
        ])->groupBy('year')->groupBy('month')->get();

And if you need to cast some varchar you could do

CAST(operacion_total as DECIMAL(9,2))

so a column in select could be

DB::raw("SUM(CAST(operacion_total as DECIMAL(9,2))) total"),

Please try this and let me know how it works :)