Utilizando views SQL no Ruby on Rails
Tags: ruby, rails, sql, views
Published: 2022-03-28 14:12:02

## Introdução
Na maior parte do tempo, o desenvolvedor Rails quando precisa de alguma informação do banco de dados, usa a API do Active Record pra fazer suas consultas, mas às vezes isso não é suficiente para trazer os dados desejados e é necessário escrever código SQL puro na aplicação. Geralmente isso significa que a informação é complexa demais pra ser obtida apenas as instruções básicas `SELECT`, `WHERE`, `JOIN`, ou então significa que ela é volátil o bastante pra não virar uma tabela fixa no banco de dados. Por exemplo, os vendedores com o maior volume de negociações no ano, uma listagem com os produtos do mercantil mais vendidos em diferentes momentos do dia ou o ticket médio dos clientes por faixa etária. Para dificultar ainda, às vezes é necessário permitir que esses dados sejam ordenados, filtrados e paginados, o que pode adicionar mais complexidade ainda para a consulta.

Uma forma de simplificar essas dores é abstrair essas consultas utilizando as views do banco de dados, que são basicamente tabelas virtuais, ou seja, os não estão persistidas no disco mas fornecem algumas facilidades das tabelas tradicionais. Com isso, ao invés de lidar sempre com uma consulta complexa do SQL, pode ser usado apenas o nome da view  para pegar as informações pretendidas. E como a view se comporta como uma tabela, é possível mapear um modelo do ActiveRecord para uma view do banco de dados com apenas algumas modificações simples e é isso que vou mostrar nesse artigo.

Pra facilitar, e também apresentar uma biblioteca que pode ser desconhecida pra alguns, vou utilizar a gem [scenic](https://github.com/scenic-views/scenic) para gerenciar as views da aplicação Rails, mas é opcional, quem não puder ou não quiser adicionar essa dependência no projeto, tudo pode ser feito tranquilamente sem ela. Quem for seguir SEM a scenic, ficam as dicas:

A criação de views é feita nas migrations utilizando o comando `execute`, por exemplo:

```ruby
class CreateMinhaView < ActiveRecord::Migration[6.1]
  def up
    execute <<-SQL
      CREATE OR REPLACE VIEW public.minha_view
      ...
    SQL
  end

  def down
    execute "DROP VIEW public.minha_view"
  end
end
```

E para armazená-las corretamente no dump do schema, é necessário mudar o seu formato para `:sq`l lá no `application.rb`, assim:

```ruby
module MeuApp
  class Application < Rails::Application
    ...
    config.active_record.schema_format = :sql
  end
end
```

Vale ressaltar que utilizarei o PostgreSQL como banco de dados, talvez para outros bancos de dados sejam necessárias algumas modificações no que vou ensinar. E quando eu me referir a views nesse artigo, serão sempre as views do banco de dados, e não as views html do projeto.

## Contexto

O problema que vamos solucionar é o seguinte: Nossa aplicação é um quadro de estatísticas que serve para registrar as conquistas dos jogadores de uma comunidade de e-sports. A modelagem é simples, é a seguinte:

![Diagrama de classe](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mxaya48c2588uv9kkux8.png)

Teremos o modelo `Player` com o atributo nome que representará o jogador da comunidade. O modelo `Trophy` servirá para representar os troféus que os jogadores poderão obter e terá o atributo de pontuação, que é o valor a ser contabilizado na classificação geral. Por exemplo:

    1º Lugar no Torneio Semanal: 100 pontos
    2º Lugar - Torneio Semanal: 50 pontos
    1º Lugar - Torneio Mensal: 500 pontos
    2º Lugar - Torneio Mensal:  250 pontos

E por fim, teremos o modelo `Achievement`, que servirá para registrar as conquistas dos jogadores, ou seja, nele será representado o jogador e qual troféu ele ganhou.

Para criar essa estrutura que descrevi, crie uma nova aplicação Rails e execute esses comandos no terminal:

```ruby
bundle exec rails g model player name
bundle exec rails g model trophy title points:integer
bundle exec rails g model achievement player:references trophy:references
bundle exec rails db:create db:migrate
```

Agora vamos preencher com alguns dados falsos para termos com o que testar. No arquivo `db/seeds.rb` coloque o seguinte código e depois execute um `bundle exec rails db:seed`:

```ruby
# Players
player_a = Player.create!(name: 'Jogador A')
player_b = Player.create!(name: 'Jogador B')
player_c = Player.create!(name: 'Jogador C')
player_d = Player.create!(name: 'Jogador D')

# Trophies
tropy_gold_estadual = Trophy.create!(
  title: 'Ouro - Torneio Estadual',
  points: 100
)
tropy_silver_estadual = Trophy.create!(
  title: 'Prata - Torneio Estadual',
  points: 50
)
tropy_gold_nacional = Trophy.create!(
  title: 'Ouro - Torneio Nacional',
  points: 500
)
tropy_silver_nacional = Trophy.create!(
  title: 'Prata - Torneio Nacional',
  points: 300
)

# Player A achievements
# 2x Gold Nacional
# 2x Gold Estadual
# Total: 1200 points
[
  tropy_gold_estadual, 
  tropy_gold_estadual, 
  tropy_gold_nacional,
  tropy_gold_nacional
].each do |trophy|
  Achievement.create!(
    player: player_a,
    trophy: trophy
  )
end

# Player B achievements
# 1x Gold Estadual
# 1x Silver Estadual
# Total: 150 points
[ 
  tropy_gold_estadual, 
  tropy_silver_estadual
].each do |trophy|
  Achievement.create!(
    player: player_b,
    trophy: trophy
  )
end

# Player C achievements
# 3x Silver Estadual
# Total: 150 points
[
  tropy_silver_estadual, 
  tropy_silver_estadual, 
  tropy_silver_estadual
].each do |trophy|
  Achievement.create!(
    player: player_c,
    trophy: trophy
  )
end

# Player D achievements
# 1x Gold Nacional
# 1x Silver Nacional
# 1x Gold Estadual
# Total: 850 points
[
  tropy_gold_nacional, 
  tropy_silver_nacional, 
  tropy_silver_estadual
].each do |trophy|
  Achievement.create!(
    player: player_d,
    trophy: trophy
  )
end
```

## Problema
Com nossa estrutura montada e com alguns dados fictícios criados, nos deparamos com a necessidade de mostrar um ranking, uma classificação geral de todos os jogadores da comunidade em uma tela. Para resolver essa consulta com a API do ActiveRecord, poderia ser feito algo assim:

```ruby
players = Player
  .select(
    <<-SQL
      players.name, 
      sum(trophies.points) as total_points
    SQL
  )
  .joins(achievements: :trophy)
  .group('players.id')
  .order('total_points DESC')

players.each do |player|
  puts player.name
  puts player.total_points
  puts '----'
end

# No console apareceria:
# Jogador A
# 1200
# ----
# Jogador D
# 850
# ----
# Jogador B
# 150
# ----
# Jogador C
# 150
```

Perceba que esse código faz um select utilizando `SUM`, 2 `joins`, um `group` e uma ordenação. Não é lá um código muito bonito de se ver espalhado pela aplicação e também não é muito fácil manter. Supondo que a comunidade de jogadores seja enorme e precise fazer uma paginação, ou a consulta evolua para algo mais personalizado que precise de algum filtro, esse código pode ficar mais complexo ainda. Para ajudar a organizar isso, vamos transferir essa consulta para uma view do banco de dados.

## Instalando a gem scenic
Para ajudar a gerenciar as views sql, vou utilizar a gem [scenic](https://github.com/scenic-views/scenic). Ela traz a vantagem de poder utilizar as views sem a necessidade de alterar o formato do schema.rb para structure.sql. De quebra fornece um versionamento dessas views facilitando o acompanhamento das modificações e as reversões pra versões anteriores. Outro ponto positivo é que todo o código da estrutura da view é armazenado em arquivos .sql, facilitando o realce da sintaxe e a execução do código em terminais ou outras ferramentas de banco de dados.

Para instalar a scenic no nosso projeto, primeiro adicione `gem 'scenic'` no arquivo `Gemfile` e depois execute um `bundle install` para finalizar a instalação.

## Criando a view no banco de dados

Para gerar uma view com a scenic é necessário executar o comando no console:

```ruby
bundle exec rails g scenic:view ranking_items
```

Esse comando cria um arquivo na pasta `db/migrations` chamado de `[TIMESTAMP]_create_ranking_items.rb` com o seguinte conteúdo:

```ruby
class CreateRankingItems < ActiveRecord::Migration[6.1]
  def change
    create_view :ranking_items
  end
end
```

E também é criado o arquivo `db/views/ranking_items_v01.sql` sem nada escrito nele. É nele que vamos colocar o código que cria nossa view:

```sql
SELECT
  players.id AS player_id,
  players.name AS player_name,
  sum(trophies.points) AS total_points
FROM
  players
INNER JOIN
  achievements 
  ON achievements.player_id = players.id
INNER JOIN
  trophies
  ON trophies.id = achievements.trophy_id
GROUP BY
  players.id
ORDER BY
  sum(trophies.points) DESC;
```

Perceba que não coloquei nenhum `CREATE OR REPLACE VIEW`, que são os comandos SQL relacionados a criação ou substuição da view, pois a scenic já entende que uma view deve ser criada com o nome ranking_items através do `create_view :ranking_items` que está na migração gerada.

Para finalizar e criar a view no banco de dados execute um `bundle exec rails db:migrate`. Com isso, se você for no terminal psql ou no seu SGBD poderá conferir o resultado que a view está gerando com a consulta `SELECT * FROM ranking_items`:

![Resultado da consulta](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/aq9vbmyy5kp32p86e2qw.png)

## Criando o modelo

Agora é a hora de mapear a view que criamos no banco de dados para um modelo do ActiveRecord. Não é muito diferente de criar um modelo que aponta para uma tabela física mas tem algumas diferenças. Na pasta `app/models` crie um arquivo chamado `ranking_item.rb` e coloque o seguinte conteúdo:

```ruby
class RankingItem < ApplicationRecord
  protected

  def readonly?
    true
  end
end
```

É necessário sobrescrever o método protegido `readonly?` para informar ao Rails e aos demais desenvolvedores que utilizando esse modelo não é permitido inserir, atualizar ou apagar informações no banco de dados, e se alguém tentar fazer essas ações receberá a exceção avisando `ActiveRecord::ReadOnlyRecord (RankingItem is marked as readonly)`. Sem essa configuração, os métodos create, update, save, destroy e afins, lançarão erros de sintaxe do SQL não muito claros.

Antes de prosseguir, só uma dica útil para quem não quer ou não pode seguir o padrão de nomeação das views de acordo com o nome do modelo. Existe a possibilidade de configurar o modelo para informar qual o nome da view que ele deve buscar os dados. Por exemplo, supondo que sua view teve que ser criada com o nome `dbviews_0001_ranking`, você terá que fazer assim no modelo:

```ruby
class RankingItem < ApplicationRecord
  self.table_name = :dbviews_0001_ranking
  # ... resto do código
end
```

Pronto, agora se abrir o console do rails com o `bundle exec rails c`, o modelo poderá ser utilizado normalmente, boa parte da API do ActiveRecord será compatível:

```ruby
RankingItem.first
# => #<RankingItem player_id: 9, player_name: "Jogador A", total_points: 1200>

RankingItem.all
# => #<ActiveRecord::Relation [#<RankingItem ...>]>

r = RankingItem.find_by(player_name: 'Jogador A')
r.total_points 
# => 1200

RankingItem.where('total_points > 800').size
# => 2

RankingItem.limit(2).offset(2)
# => #<ActiveRecord::Relation [#<RankingItem ...>]>
```

Outra vantagem é que posso também usar as associações do Rails para integrar com outros modelos. Por exemplo, um `ranking_item` pertence a um `player`. É totalmente possível adicionar um `belongs_to :player` na classe `RankingItem` e acessar o jogador dessa forma:

```ruby
r = RankingItem.first
r.player
# => #<Player id: 9, name: "Jogador A", created_at: ...
```

Quase pronto, mas acho que seria bom mostrar a posição do jogador na classificação e percebi que a view que criei não foi criada com essa coluna. Como fazer isso?

## Modificando views já existentes

A scenic também auxilia quando uma view precisa ter sua estrutura alterada. O comando é o mesmo que é utilizado para criar a view, ou seja: `bundle exec rails g scenic:view ranking_items`. Isso gerará um arquivo de migração chamado `[TIMESTAMP]_update_ranking_items_to_version_2.rb` com o seguinte conteúdo:

```ruby
class UpdateRankingItemsToVersion2 < ActiveRecord::Migration[6.1]
  def change
    update_view :ranking_items, version: 2, revert_to_version: 1
  end
end
```

A diferença aqui é que ao invés do `create_view` que foi gerado na primeira vez que executamos o comando, agora tem um `update_view`, a próxima versão que deverá ser utilizada quando a migração for executada e qual a versão deverá voltar em caso de reversão dessa migração. Esse `update_view` fará um `DROP VIEW` e criará uma nova já atualizada, mas nem sempre é possível por conta de dependências entre views. Se um dia passar por essa situação e desejar utilizar um `CREATE OR REPLACE VIEW`, troque o `update_view` por `replace_view`.

O comando também gerou o arquivo `db/views/ranking_items_v02.sql` já preenchido com o código da primeira versão. Na nova versão, vou adicionar a coluna rank utilizando a função `RANK` do SQL que representará a posição do jogador, e também deixarei de ordenar pela soma dos pontos e passarei a ordenar pela nova coluna `rank`. A v2 ficará assim:

```sql
SELECT
  RANK() OVER(ORDER BY sum(trophies.points) DESC) AS rank,
  players.id AS player_id,
  players.name AS player_name,
  sum(trophies.points) AS total_points
FROM
  players
INNER JOIN
  achievements
  ON achievements.player_id = players.id
INNER JOIN
  trophies
  ON trophies.id = achievements.trophy_id
GROUP BY
  players.id
ORDER BY
  rank;
```

Execute o `bundle exec rails db:migrate` e você terá sua view atualizada. Se fizer o select na tabela verá o novo campo com a posição do jogador:

![Resultado da consulta](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rer7s5mrfxfavs9d5lbi.png)

Mas e se a consulta da nossa view fosse tão complexa, em tabelas enormes quantidades de registros que não fosse possível utilizar uma view comum e fosse necessária uma view materializada? O scenic também tem opções pra esses casos.

## Materializando a view
Caso você precise de uma view materializada, os comandos para gerá-la são os mesmos que eu passei até agora, com a diferença que a flag `—-materialized` deverá estar presente. Por exemplo: `rails g scenic:view nome_da_view --materialized`.

Para atualizar os dados da view materializada a scenic disponibiliza o método `Scenic.database.refresh_materialized_view`. Então é possível você criar um método de classe no seu modelo para facilitar o uso, fica assim:

```ruby
class MeuModelo < ApplicationRecord
  def self.refresh
    Scenic
      .database
      .refresh_materialized_view(
        'nome_da_view_materializada', 
        concurrently: false, 
        cascade: false
      )
  end

  protected

  def readonly?
    true
  end
end
```

## Conclusão
É isso, apresentei mais uma solução que vale sempre a pena ter em mãos para quando situações como as apresentadas surgirem no dia a dia. Lembrando sempre, que nem sempre essa é a melhor opção para solucionar todos os problemas, às vezes faz mais sentido criar uma tabela física mesmo ou então usar o padrão Query Objects que falarei posteriormente sobre ele aqui no blog.