Posted on: June 29, 2022 12:21 AM
Posted by: Renato
Categories: eloquent collections Laravel postgresql
Views: 1013
# Otimizando o cálculo de totais no Laravel usando agregação condicional
## Create Table PostgreSQL
```sql
CREATE TABLE IF NOT EXISTS public.orders
(
id serial PRIMARY KEY,
customer character varying(255) NOT NULL,
status character varying(100) NOT NULL,
price numeric(15,2),
created_at timestamp(0) without time zone,
updated_at timestamp(0) without time zone
);
```
## Insert
```sql
INSERT INTO "public"."orders" ("customer", "status", "price", "created_at", "updated_at") VALUES ('Edna Cardoso', 'canceled', '3602497', '2022-06-28 17:53:20', '2022-06-28 17:53:23');
INSERT INTO "public"."orders" ("customer", "status", "price", "created_at", "updated_at") VALUES ('Renato Lucena', 'approved', '480832', '2022-06-28 17:53:20', '2022-06-28 17:53:23');
INSERT INTO "public"."orders" ("customer", "status", "price", "created_at", "updated_at") VALUES ('Alexandre Philype', 'finished', '1000', '2022-06-28 17:53:20', '2022-06-28 17:53:23');
INSERT INTO "public"."orders" ("customer", "status", "price", "created_at", "updated_at") VALUES ('Jonathan Costa', 'shipped', '20987316', '2022-06-28 17:53:20', '2022-06-28 17:53:23');
INSERT INTO "public"."orders" ("customer", "status", "price", "created_at", "updated_at") VALUES ('Mrs.Leidiany Lucena', 'approved', '1000', '2022-06-28 17:53:20', '2022-06-28 17:53:23');
INSERT INTO "public"."orders" ("customer", "status", "price", "created_at", "updated_at") VALUES ('Thiago Costa', 'finished', '1000', '2022-06-28 17:53:20', '2022-06-28 17:53:23');
INSERT INTO "public"."orders" ("customer", "status", "price", "created_at", "updated_at") VALUES ('Marcos Costa', 'approved', '4747', '2022-06-28 17:53:20', '2022-06-28 17:53:23');
INSERT INTO "public"."orders" ("customer", "status", "price", "created_at", "updated_at") VALUES ('Francisco Jose de Lucena', 'shipped', '767992', '2022-06-28 17:53:20', '2022-06-28 17:53:23');
INSERT INTO "public"."orders" ("customer", "status", "price", "created_at", "updated_at") VALUES ('Josefa de O. Lucena', 'finished', '102895', '2022-06-28 17:53:20', '2022-06-28 17:53:23');
```
Nesse artigo vou falar sobre algo que todo dev já deve ter trabalhado na vida: otimização/melhoria de consultas SQL no backend. Mais especificamente, a otimização de consultas de cálculos de totais.
O banco de dados executa todas as consultas de forma coletiva e existem limites (conexões, CPU, memória), que definem o número de consultas que pode ser executado de uma vez. Assim, todos endpoints da sua aplicação são importantes na perspectiva do desempenho do banco de dados.
Devemos tratar o BD como um recurso compartilhado por todos os usuários da sua aplicação, onde cada consulta executada pelo banco de dados utiliza um pouco desse recurso finito. Rode muitas consultas pesadas ao mesmo tempo e ele pode chegar no limite.
Então, precisamos encontrar as consultas que demandam mais recursos e otimizá-las. Normalmente essas consultas são aquelas que calculam algum tipo de total e principalmente quando múltiplos totais são necessários na mesma requisição. Vamos ao exemplo:
Digamos que temos uma tabela no banco de dados chamada orders com dados neste formato:
--|------------------------|--------|-----------|-------------------|-------------------|
1|Renato Lucena |approved| 480832.00|2022-06-28 17:53:20|2022-06-28 17:53:23|
3|Edna Cardoso |canceled| 3602497.00|2022-06-28 17:53:20|2022-06-28 17:53:23|
4|Alexandre Philype |finished| 1000.00|2022-06-28 17:53:20|2022-06-28 17:53:23|
5|Jonathan Costa |shipped |20987316.00|2022-06-28 17:53:20|2022-06-28 17:53:23|
6|Mrs.Leidiany Lucena |approved| 1000.00|2022-06-28 17:53:20|2022-06-28 17:53:23|
7|Thiago Costa |finished| 1000.00|2022-06-28 17:53:20|2022-06-28 17:53:23|
8|Marcos Costa |approved| 4747.00|2022-06-28 17:53:20|2022-06-28 17:53:23|
9|Francisco Jose de Lucena|shipped | 767992.00|2022-06-28 17:53:20|2022-06-28 17:53:23|
10|Josefa de O. Lucena |finished| 102895.00|2022-06-28 17:53:20|2022-06-28 17:53:23|
Desejamos mostrar os seguintes totais de pedidos para o usuário:
Aprovados: X
Finalizados: X
Cancelados: X
Enviados: X
Total: X
Uma das maneiras mais fáceis para realizar o cálculo desses totais é a seguinte:
public function index()
{
$total = Order::count();
$approved = Order::where('status', 'approved')->count();
$finished = Order::where('status', 'finished')->count();
$canceled = Order::where('status', 'canceled')->count();
$shipped = Order::where('status', 'shipped')->count();
dd($canceled);
}
O problema dessa maneira é a execução de cinco consultas no banco de dados, o que sabemos que não é muito bom. Pra resolver, podemos tentar otimizar o código utilizando as Collections do Eloquent, ficaria assim:
public function collection()
{
$orders = Order::all();
$total = $orders->count();
$approved = $orders->where('status', 'approved')->count();
$finished = $orders->where('status', 'finished')->count();
$canceled = $orders->where('status', 'canceled')->count();
$shipped = $orders->where('status', 'shipped')->count();
dd($approved);
}
```
Com essa otimização temos apenas uma consulta no banco de dados. Problema resolvido, então? Nope! Esse método é pior do que o anterior. Isso ocorre pois, apesar de termos somente uma consulta ao banco de dados, a contagem dos resultados está sendo executada pelo Collection do Eloquent, ou seja, pelo PHP.
## Agregação condicional
Nesse caso, a agregação condicional pode ser utilizada para calcular todos os totais com somente uma consulta, como no exemplo:
select
count(*) as total,
count(case when status = 'approved' then 1 end) as approved,
count(case when status = 'finished' then 1 end) as finished,
count(case when status = 'canceled' then 1 end) as canceled,
count(case when status = 'shipped' then 1 end) as shipped
from orders;
Resultado da execução da consulta:
-----|--------|--------|--------|-------|
9| 3| 3| 1| 2|
### Convertendo essa consulta SQL para o Eloquent temos:
public function eloquent()
{
->selectRaw('count(*) as total')
->selectRaw("count(case when status = 'approved' then 1 end) as approved")
->selectRaw("count(case when status = 'finished' then 1 end) as finished")
->selectRaw("count(case when status = 'canceled' then 1 end) as canceled")
->selectRaw("count(case when status = 'shipped' then 1 end) as shipped")
->first();
dd($totals);
}
```
## Muito massa né?
Tente usar as agregações condicionais na próxima vez que criar um painel ou algum outro tipo de resumo de dados em sua aplicação. Seu banco de dados vai ficar feliz!
Abaixo temos a comparação do desempenho das 3 maneiras apresentadas para cálculo de totais, utilizando um banco de dados com 500mil registros:
- 5 consultas separadas: 0.14 ms;
- 1 consulta e count no Eloquent Collections: 20.14 ms;
- 1 consulta usando agregação condicional: 0.09 ms;
Lembre-se de que qualquer consulta que você possa fazer com mais rapidez ou evitar totalmente ajudará o desempenho de todo o sistema. Toda consulta é importante!
## Fonte:
- https://medium.com/devorando/otimizando-o-c%C3%A1lculo-de-totais-no-laravel-lumen-usando-agrega%C3%A7%C3%A3o-condicional-ed6f74f49c53
Donate to Site
Renato
Developer