How to get the SUM of a column based on the common value(id) in other column in octobercms

Question

i get stuck here. Hope someone can help. i have a database that store the purchase done users on any project.

here is my table in twig

                       <tbody>
                             {% for paid_log in paid_logs %}
                              {% paid_log.user_id == user.id and paid_log.status == 2 %}
                                    <tr>
                                        <td>{{paid_log.project.name }}</td> 
                                        <td>{{paid_log.amount }}</td>
                                         
                                    </tr>
                           
                             {% endif %}
                             {% endfor %}

                       </tbody>

Results

|Project ID| Purchase Amount|

1          |1000
1          |1010
2          |2111
4          |9954
1          |9871
4          |6121 

I want to loop through the database and sum up the purchase amount where the project ID is the same?

Expecting something like this:

Project ID |Purchase Total
1          |11881
2          |2111
4          |16075

Answer 1

While possible, Twig isn't ideal for doing calculations like this. The code gets pretty messy. Instead, I would suggest creating the data structure In the onStart() where you define paid_logs.

Example:

function onStart()
{
    // ... define $paid_logs and $user
    $data = [];
    foreach ($paid_logs as $paid_log) {
        if ($paid_log->user_id == $user->id && $paid_log->status == 2) {
            if (!isset($data[$paid_log->project_id])) {
                $data[$paid_log->project->id] = 0;
            }
            $data[$paid_log->project->id] += $paid_log->amount;
        }
    }
    $this['data'] = $data;
}

Then, in your Twig you can simplify:

{% for project_id, amount in data %}
    {{ project_id }} | {{ amount }}
{% endfor %}