Use sum with Eloquent

Navigation
1

I have the following query: 

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 would like to use this sum there in Laravel

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

In my controller it looks 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 returning 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 the client and the items), but then I do not know how to do the 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?

    
asked by anonymous 24.11.2017 / 18:00

1 answer

1

Because the Client and Item values are coming null because, in the Select done does not have the fields for 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 select the two fields of the relation that are cliente and item , without these fields the

24.11.2017 / 19:13
Error in form django Check empty directories and catch command output