Laravel-按月获取数据并求和(varchar) [英] Laravel - Get data by Month and sum it (varchar)

问题描述

我也看到过类似的问题，但老实说，我没有发现任何可以使我正确使用的方法，这是因为我也在寻找一种更好的方法来做到这一点.

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.

在我的应用程序中的某些时候，我正在使用chartjs，我需要在其中显示月份，然后显示值(销售额).为此，我知道(或我认为)我必须按月检索分组的数据，而到目前为止，我到了这里:

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');
    });

好吧...如您所见，我什么都没有求和，那是因为在我的数据库中，我的值被存储在varchar中(带点号！)，我知道这不是正确的处理方式，但是此时更改所有内容对我来说现在不是解决方案，所以我该如何更改该功能以删除点并执行适当的金额?我已经尝试过使用CAST，但是它给了我未定义的功能.

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.

-编辑-

大家好，我想我已经快到了，我不知道 transform函数，在那里我可以对preco(price)字段进行一些修改，但是仍然存在问题因为是varchar，所以我需要删除点号才能使总和起作用，我所拥有的是:

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

但是这不起作用，因为$ nStr是字符串...我需要做什么?如果我转换为浮点数，则总和将不起作用...如果我这样离开，则175.000和1.000.000之间的总和将为176.000，这是错误的...有什么想法吗?

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?

推荐答案

朋友，如果您想使用集合进行分组，并且还要对分组中的值求和，我认为为时不晚.

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

让我们使用以下示例:

我将使用以下内容对您的雄辩查询做一个示例结果...说我们有用户

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...

更新

如果记录很大，则可以使用访问器

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

您可能会有以下内容，可以像这样格式化您的模型中的"preco"字段:

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

这意味着当Laravel构建Eloquent对象时，该值将按照指定的格式设置，这意味着您可以从结果中删除第一个map().

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:保存时，您可能需要一个变数器才能将其更改为数据库中所需的格式.

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

干杯！

这篇关于Laravel-按月获取数据并求和(varchar)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！