Laravel sort items from one table from the values of another

Question

I have two tables, a call proposals and another propostas_items

propostas
-id
-nome
-descricao

propostas_itens
-id
-proposta_id
-quantidade
-valor

A proposal has many items, I need to list the proposals according to their total value, so I have to sum the value of all the items related to a proposal and make an orderby for this value, but I’m not able to do this in Laravel I use version 5.7

I tried to do a Join() and then a groupBy() but I can’t group the results and then operate them in orderby()

$propostas->join('propostas_itens', 'propostas_itens.proposta_id', '=', 'propostas.id')->groupBy('propostas_itens.proposta_id');

I’m grateful for all your help!

Answer 1

Can do in the inverse of the relationship and first seek to group the propostas_itens and with the command with call the relationship of proposta, example:

App\PropostaItens::groupBy('proposta_id')
      ->selectRaw('proposta_id,sum(valor)')
      ->orderBy('sum(valor)')
      ->with('proposta')
      ->get()

the result is as follows:

=> Illuminate\Database\Eloquent\Collection {#2850
 all: [
   App\PropostaItens {#2858
     proposta_id: 2,
     sum(valor): "50.00",
     proposta: App\Proposta {#2869
       id: 2,
       nome: "P2",
       descricao: "P2",
     },
   },
   App\PropostaItens {#2855
     proposta_id: 3,
     sum(valor): "190.25",
     proposta: App\Proposta {#2870
       id: 3,
       nome: "P3",
       descricao: "P3",
     },
   },
   App\PropostaItens {#2840
     proposta_id: 1,
     sum(valor): "200.00",
     proposta: App\Proposta {#2864
       id: 1,
       nome: "P1",
       descricao: "P1",
     },
   },
 ],
}

when using do so:

foreach($result as $r)
{
    echo $r->proposta->id;
    echo $r->proposta->nome;
    echo $r->proposta->descricao;
}