Calculation of two BD fields in a function

Question

Good Afternoon, to calculate the budget of my works I use a query in my controller ode makes the sum of the fields , being few records there is no problem but if there are 500, 1000 records this becomes a big problem in the project, I’d like to do a function in jquery, but it’s a little tricky around here. Thank you for your attention.

Controller :

public function index()
{
    $services = Service::with('client')->orderBy('created_at', 'DESC')->get();

    $budgets=DB::select('SELECT  services.id  , cast(sum(mats.qtd*mats.price+quantityHours*hoursPrice)as decimal (5,2))as BUDGET
                                FROM mats_por_servicos AS mats 
                                INNER JOIN services as services ON mats.serv_id= services.id 
                                group by mats.serv_id, services.id ;');

    return view('services.index',compact('services','budgets'));
}

Table :

<table class="table table-striped table-bordered">
    <thead>
        <tr >

            <th width="20%" class="text-center " scope="col">Cliente</th>
            <th class="text-center" scope="col">Morada do Cliente</th>
            <th width="25%" class="text-center" scope="col">Obra</th>
            <th width="10%" class="text-center" scope="col">Orçamento</th>
            <th width="15%" class="text-center" scope="col">Data</th>
        </tr>
    </thead>
    <tbody>
        @foreach($services as $service)
            <?php
                $id = $service->id;
                $prt_budget = 0;
                foreach($budgets as $b )
                {
                    if($b->id == $id )
                    {
                        $prt_budget = $b->BUDGET;
                        break;
                    }
                }?>

                <tr>
                    <td class="text-center"><a href="/clients/{{$service->client->id}}">{{$service->client->firstName}} {{$service->client->secondName}}</a></td>
                    <td class="text-center">{{$service->client->address}}</td>
                    <td class="text-center" id="select_serv"><a href="/services/{{$service->id}}">{{$service->designation}}</a></td>
                    <td class="text-center" id="budget">{{$prt_budget}} €</td>{{--tem que listar o orçamento respetivo ao serviço--}}
                    <td class="text-center">{{$service->recordDate}}</td>
                </tr>

            @endforeach
        </tbody>
    </table>`

BD model:

Upshot:

Answer 1

From what I’ve seen of your structure, you could try to do two things:

1 - In the table mats_por_servicos, check that the fields mat_id and serv_id are indexed. 2 - In your query, you made a CAST to format the values. This is something that can impact performance. Try to remove the CAST from your query and format the values in the report.