Use sum with Eloquent

Question

I have the following appointment:

SELECT P.ID
      ,P.NR_CRACHA     CRACHA
      ,P.NM_CLIENTE     NOME
      ,E.DS_EMPRESA    EMPRESA
      ,SUM( R.VL_PRECO ) VALOR
 FROM registro R
      JOIN pessoa   P
      JOIN empresa E
      WHERE P.CD_PESSOA  = R.CD_PESSOA
  AND E.CD_EMPRESA = P.CD_EMPRESA   
  AND (P.NM_PESSOA LIKE :pessoa OR P.NR_CRACHA LIKE :cracha)
  AND R.SN_PAGO = 'N'
 GROUP BY P.CD_PESSOA

I’d like to use that sum there in Laravel

My registration model looks like this:

class Registro extends Model
{
    //
    public function clientes(){
        return $this->belongsTo( 'App\Cliente', 'cliente', 'id' );
    }

    public function itens(){
        return $this->belongsTo( 'App\Item', 'item', 'id' );
    }
}

On my controller it’s like this:

public function listRegistro(){

        $registro = Registro::with([ 'clientes', 'itens'] )
                                    ->select( DB::raw('sum( vl_preco ) as valor') )
                                    ->where( 'sn_pago', 'N' )
                                    ->groupBy('cliente')
                                    ->paginate(10);

        $cliente = Cliente::all();
        $item    = Item::all();

        return response()->json( $registro );


    }

But it’s coming back like this:

    {
  "total": 1,
  "per_page": 10,
  "current_page": 1,
  "last_page": 1,
  "next_page_url": null,
  "prev_page_url": null,
  "from": 1,
  "to": 1,
  "data": [
    {
      "valor": "10.00",
      "clientes": null,
      "itens": null
    }
  ]
}

Customers and null items

If I try like this:

$registro =  Registro::with(['clientes', 'itens'])
                        ->join( 'clientes', 'registros.cliente','=','clientes.id' )
                        ->join( 'item', 'registros.item','=','item.id' )
                        ->get();

The data comes (from customer and items), but then I don’t know how to do sum with groupBy

 [
  {
    "id": 1,
    "cliente": 1,
    "item": 1,
    "vl_preco": "10.00",
    "sn_pago": "N",
    "qt_compra": 1,
    "created_at": "2017-11-23 20:16:17",
    "updated_at": "2017-11-23 20:16:17",
    "nm_cliente": "Carlos Bruno",
    "nr_cracha": "4142",
    "empresa": 2,
    "nr_cep": "69084100",
    "nr_casa": "22",
    "ds_complemento": "Casa",
    "dt_cadastro": "2017-11-23",
    "email": "[email protected]",
    "senha": "$2y$10$uhsh2MjlYNZcjqiiQb.JBO3d3jD1KX8PUnQWLzB8qgx0sCnKuKidi",
    "sn_senha_atual": "N",
    "ds_item": "Prato Feito",
    "vl_item": "10.00",
    "clientes": {
      "id": 1,
      "nm_cliente": "Carlos Bruno",
      "nr_cracha": "4142",
      "empresa": 2,
      "nr_cep": "69084100",
      "nr_casa": "22",
      "ds_complemento": "Casa",
      "dt_cadastro": "2017-11-23",
      "email": "[email protected]",
      "senha": "$2y$10$uhsh2MjlYNZcjqiiQb.JBO3d3jD1KX8PUnQWLzB8qgx0sCnKuKidi",
      "sn_senha_atual": "N",
      "created_at": "2017-11-23 20:17:09",
      "updated_at": "2017-11-23 20:17:09"
    },
    "itens": {
      "id": 1,
      "ds_item": "Prato Feito",
      "vl_item": "10.00",
      "created_at": "2017-11-23 20:16:17",
      "updated_at": "2017-11-23 20:16:17"
    }
  }
]

Can you help me?

Answer 1

Because the values of Client and Item are coming null, because, in Select done does not have the fields for which the eloquent make the relation and load the values, how to solve?

public function listRegistro()
{

    $registro = Registro::with([ 'clientes', 'itens'] )
                    ->select( DB::raw('sum( vl_preco ) as valor, cliente, item') )
                    ->where( 'sn_pago', 'N' )
                    ->groupBy('cliente')
                    ->paginate(10);       

    return response()->json( $registro );

}

Solution is to put in the select the two fields of the relationship that are cliente and item, without these fields the eloquent cannot make the relationship and bring the data.