Utilizando views SQL no Ruby on Rails

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 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:

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 :sql lá no application.rb, assim:

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:

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:

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:

# 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:

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. 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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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.