Artigos - Artigos e dicas feito com PHP + Laravel

Posted on: January 29, 2020 12:18 PM

Posted by: Renato

Categories: Variados

Views: 420

Calculando totais no Laravel usando agregados condicionais

Calcular múltiplos totais (agregados) no Laravel da maneira mais eficiente. Por exemplo, talvez você tenha um serviço de assinatura de e-mail e queira exibir vários totais com base em seus inscritos:

Idealmente, gostaríamos de calcular esses valores usando uma única consulta de banco de dados. No entanto, se você não tiver certeza de como fazer isso, você pode facilmente encontrar o caminho mais fácil e fazer isso:

$total = Subscriber::count();
$confirmed = Subscriber::where('status', 'confirmed')->count();
$unconfirmed = Subscriber::where('status', 'unconfirmed')->count();
$cancelled = Subscriber::where('status', 'cancelled')->count();
$bounced = Subscriber::where('status', 'bounced')->count();

Mas, claro, isso resultará em cinco consultas ao banco de dados, que sabemos que não são ótimas. Então você pode até tentar ser inteligente e resolver isso de outra maneira:

$subscribers = Subscriber::all();
$total = $subscribers->count();
$confirmed = $subscribers->where('status', 'confirmed')->count();
$unconfirmed = $subscribers->where('status', 'unconfirmed')->count();
$cancelled = $subscribers->where('status', 'cancelled')->count();
$bounced = $subscribers->where('status', 'bounced')->count();

Aqui estamos fazendo uma única consulta de banco de dados para obter todos os assinantes e, em seguida, executando contagens na coleção resultante. O problema é que essa abordagem é significativamente pior que a solução de várias consultas. Se nosso aplicativo tiver milhares ou milhões de inscritos, o tempo para processar todos os registros será lento e usará uma tonelada de memória.

Agregados condicionais

Na verdade, existe uma maneira realmente simples de calcular esses totais usando uma única consulta de banco de dados. O truque é colocar condições dentro de funções agregadas. Aqui está um exemplo no SQL:

select
count(*) as total,
count(case when status = 'confirmed' then 1 end) as confirmed,
count(case when status = 'unconfirmed' then 1 end) as unconfirmed,
count(case when status = 'cancelled' then 1 end) as cancelled,
count(case when status = 'bounced' then 1 end) as bounced from subscribers

total | confirmed | unconfirmed | cancelled | bounced
------+-----------+-------------+-----------+---------
  200 |       150 |          50 |        30 |   25

Veja como você pode escrever essa consulta no Laravel usando o construtor de consultas:

$totals = DB::table('subscribers')
    ->selectRaw('count(*) as total')
    ->selectRaw("count(case when status = 'confirmed' then 1 end) as confirmed")
    ->selectRaw("count(case when status = 'unconfirmed' then 1 end) as unconfirmed")
    ->selectRaw("count(case when status = 'cancelled' then 1 end) as cancelled")
    ->selectRaw("count(case when status = 'bounced' then 1 end) as bounced")
    ->first();

<div>Total: {{ $totals->total }}</div>
<div>Confirmed: {{ $totals->confirmed }}</div>
<div>Unconfirmed: {{ $totals->unconfirmed }}</div>
<div>Cancelled: {{ $totals->cancelled }}</div>
<div>Bounced: {{ $totals->bounced }}</div>

Boolean columns

Essa abordagem é ainda mais fácil se você estiver usando colunas booleanas. Um bom caso de uso para isso é totalizar diferentes funções dentro de um aplicativo.

$totals = DB::table('subscribers')
    ->selectRaw('count(*) as total')
    ->selectRaw('count(is_admin or null) as admins')
    ->selectRaw('count(is_treasurer or null) as treasurers')
    ->selectRaw('count(is_editor or null) as editors')
    ->selectRaw('count(is_manager or null) as managers')
    ->first();

Isso funciona porque o agregado count ignora as colunas nulas. Ao contrário do PHP, onde false ou null retorna false, no SQL (e JavaScript) ele retorna null. Basicamente, A ou B retorna o valor A se A puder ser coagido para true; caso contrário, retorna B.

Filter clauses

Finalmente, se você estiver usando o PostgreSQL, você também pode usar cláusulas de filtro para conseguir isso. Cláusulas de filtro são ótimas, pois permitem que você escreva suas condições usando cláusulas where normais. Além disso, com base em meus testes, as cláusulas de filtro são realmente mais rápidas do que as abordagens mencionadas acima.

$totals = DB::table('subscribers')
    ->selectRaw('count(*) as total')
    ->selectRaw('count(*) filter (where is_admin) as admins')
    ->selectRaw('count(*) filter (where is_treasurer) as treasurers')
    ->selectRaw('count(*) filter (where is_editor) as editors')
    ->selectRaw('count(*) filter (where is_manager) as managers')
    ->first();

Se você tiver alguma pergunta ou comentário sobre este artigo, envie-me (@cpdrenato) uma mensagem no Twitter. Eu adoraria ouvir de você.

Renato

Developer

No comments yet! Be the first to comment