How to use the SUM in the Laravel?

Asked

Viewed 636 times

-1

MODEL CUSTOMERS

 public function titulo()
    {
        return $this->hasMany('App\Models\Titulo');
    }

MODEL TITLE

   public function cliente()
        {
            return $this->belongsTo('App\Models\Cliente');
        }

MIGRATION TITLES

public function up()
    {
        Schema::create('titulos', function (Blueprint $table) {

           // cadastro padrão
            $table->increments('id');
            $table->string('referencia');

            $table->integer('tipo_id')->unsigned();
            $table->foreign('tipo_id')->references('id')->on('titulo_tipos');

            $table->integer('cliente_id')->unsigned();
            $table->foreign('cliente_id')->references('id')->on('clientes');

            $table->integer('devedor_id')->unsigned();
            $table->foreign('devedor_id')->references('id')->on('devedors');

            $table->integer('user_id');

            $table->date('vencimento_titulo')->nullable();
            $table->double('valor')->nullable();
            $table->string('status')->default('pendente');
            $table->timestamps();

            // fim cadastro padrão


        });

MY CODE:

DB::table('titulos')
        ->join('clientes', 'titulos.cliente_id', '=', 'clientes.id')
        ->select('cliente_id', DB::raw('SUM(valor) as total'))
        ->groupBy('cliente_id')
        ->get();

I have two related tables ( Client and Titles ), I would like to assemble a query per client with the sum of all the titles bringing fields from the client table, as I do in this aggregation with the SUM?

Desired result:

CLIENTE.NOME |CLIENTE.CNPJ         | SUM(TITULOS.VALOR)
cliente A    | 01.200.300/00001-28 | R$ 8000      
cliente B    | 01.200.300/00001-28 | R$ 15000

INSIDE THE BANK WORKS SMOOTHLY:

SELECT 
clientes.id,
clientes.nome,
clientes.cnpj,
(SELECT SUM(titulos.valor) FROM titulos where titulos.cliente_id = clientes.id ) as total
from
clientes ;
php laravel laravel-5 laravel-eloquent

  • What mistake are you getting?

  • Do the reverse by table of titles loading the customer data is much simpler

  • If it’s just the sum you want at the end of the query puts ->Count();

  • If you want to use in select use this way ->select( DB::raw("SUM(column) the sums"));

1 answer

0

To return the record sum at the end of each query put ->coun() as shows example:

DB::table('titulos')
        ->join('clientes', 'titulos.cliente_id', '=', 'clientes.id')
        ->groupBy('cliente_id')
        ->get()->count();

If in select follow the example:

DB::table('titulos')
        ->join('clientes', 'titulos.cliente_id', '=', 'clientes.id')
        ->select('cliente_id', \DB::raw("SUM(valor) as total"))
        ->groupBy('cliente_id')
        ->get();

Another thing I suggest you do is use Float instead of Double.

Browser other questions tagged php laravel laravel-5 laravel-eloquent

You are not signed in. Login or sign up in order to post.