Add data from "pivot" table of relation Many to Many

Asked

Viewed 81 times

1

I have 3 tables (clientes,seguradoras e clientes_seguradoras) that relate through 2 Models (Cliente e Seguradora) in Laravel

Each customer can have several insurers, and for each insurer he can add his price.

Tables follow the following structure:

clientes:
- nome,
- sobrenome
.....

seguradoras:
- nome,
- imagem
.....

clientes_seguradoras:
- cliente_id,
- seguradora_id,
- preco <-- valor que quero somar
.....

The relationship between client and insurer is made as follows in the model Cliente:

public function seguradoras()
    {
        return $this->belongsToMany('App\Models\Seguradora', 'clientes_seguradoras', 'cliente_id', 'seguradora_id')
            ->withPivot(['preco']);
    }

Use the model Cliente with insurers as follows Controller responsible for carrying out searches:

$clientes = Cliente::with(['seguradoras'])
// .... algumas condições where() em cima do cliente
    ->paginate(50);

But I can only do the sum by doing a "in hand" foreach inside the view itself with the data from paginate(50), only that the paginate is limited to the results displayed on the page and not to the total result of the search (model query), and return all the results at once and make a foreach is not efficient and consumes a lot of memory, as there are thousands

How can I do a query to sum the total price of the pivot table by keeping any "Where" I do on top of the client model?

php laravel laravel-eloquent

  • Do you want to get out which result? (for example line) would be better ai a Join with groupby ...

  • I wanted the float of the sum, I solved with a Join and group by on top of the Customer model, but I wanted to know if there is any more "elegant" solution with Eloquent

  • how did you do? one can create Scope etc ... but, face the Builder is equal and a pull of the Eloquent

2 answers

0

In Laravel 7 or earlier there is no simplified form of the framework to make this query, but using Eloquent it is possible to do as follows:

class Cliente extends Model
{
  public function seguradoras()
  {
    return $this->belongsToMany(Seguradora::class, 'clientes_seguradoras', 'cliente_id', 'seguradora_id');
    // removi o with ->withPivot()
  }
}

Cliente::with(['seguradoras' => function ($query) {
  $query->selectRaw('sum(`clientes_seguradoras`.`preco`) as sum_preco')
    ->groupBy('clientes_seguradoras.cliente_id')
    ->groupBy('clientes_seguradoras.seguradora_id');
}])->get();

The Laravel 8 included a method withSum and from what I understood from the documentation the consultation would be:

Cliente::with(['seguradoras' => function ($query) {
  $query->withSum('pivot', 'preco'); // talvez precise dos group by
}])->get();

-1

Is portable?

If yes, you do not need to suffer much for it in your model you will create the following attribute:

protected $appends = [ 'soma_total' ];

Still in your model you will create the following function:

public function getSomaTotalAttribute(): string
{
    // aqui tu faz sua mágica utilizando o objecto $this
}

Ai when you do any query is pivot, get, has Many, has one will automatically return one soma_total attribute.

Attention: only do this also if the case this attribute for you is somehow extremely necessary as the model at all, if you only need to have this sum at specific times it is more advisable to do it in a Pository.

Browser other questions tagged php laravel laravel-eloquent

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