SQL Orientado a Objetos

O nome parece estranho, mas um ORM, dependendo de como ele for implementado, pode ser usado exatamente para isso.

Estou trabalhando numa lib em Scala chamada relational, na qual eu pretendo fazer um SQL inteiro virar um objeto Scala. Mais ou menos o que o Arel tenta fazer, porém de forma esquisita (meio compatível com Rails, meio compatível com álgebra relacional, e não 100% nada). Mas isso fica pra um outro momento…

No post anterior, eu falei bastante sobre SQL, e sobre todas as coisas que podemos fazer ao saber montar uma query. A idéia agora é tentar montar, de fato, uma query, mas com mais do que apenas fragmentos SQL, mas com o próprio ORM.

Vamos pensar que temos uma tabela de usuários, e uma de números de telefones. O número pertence a um usuário, um usuário tem muitos números de telefone (nada de “join-tables” e coisas mais complexas por agora). Digamos que eu queira saber números de telefone possuem o mesmo prefixo (os primeiros quatro números-vamos ignorar, por hora, os nono dígito para deixar o código mais fácil) de um determinado número.

A idéia, num primeiro momento, é fazer o código para um único número. Vamos, por simplicidade, deixar isso na classe de Telephone mesmo:

class User < ActiveRecord::Base
  has_many :telephones
end

class Telephone < ActiveRecord::Base
  belongs_to :user
  
  def self.same_prefix_of(telephone)
    where('SUBSTR(telephones.number, 0, 5) = ?', telephone.number[0...4])
  end
end

#Para usar:
Telephone.same_prefix_of(Telephone.first)

Por hora, tudo bem. Um código simples, porém é agora que a coisa começa a ficar divertida: generalização

Para um método ser realmente útil, é ideal que ele seja reaproveitável. Se ele for reaproveitável, é ideal que ele seja genérico o suficiente para atender mais de um problema. Como fazer isso?

Podemos usar a velha idéia de “convenção” ou “contrato”: vamos fazer um contrato com nosso código de que sempre que formos buscar o mesmo prefixo de outro telefone, não é necessário passar EXATAMENTE um telefone: podemos, por exemplo, passar um objeto que tenha um campo chamado “number”, e o resultado serão os telefones que tem o mesmo prefixo dos telefones que passamos para o método.

Para fazer isso, precisaremos cruzar o número de telefone que passamos com todos os números. A melhor forma de fazermos isso é com um INNER JOIN. Infelizmente, o ActiveRecord NÃO nos auxilia a criar joins, a não ser que já os tenhamos definido com has_many e belongs_to. Para facilitar nossa vida, vou escrever um código chamado “inner_join”, que fará o JOIN de uma busca qualquer, baseado numa condição:

class Telephone < ActiveRecord::Base
  belongs_to :user

  def self.inner_join(table, condition)
    condition = condition.to_sql if condition.respond_to? :to_sql
    table = table.to_sql if table.respond_to? :to_sql
    join("INNER JOIN #{table} ON #{condition}")
  end
end

Com isso, podemos fazer nossa query. A idéia é que vamos passar uma relação (ActiveRecord::Relation) para o método, então vamos ter que fazer o join não com uma tabela específica, mas com a query que estamos passando:

class Telephone < ActiveRecord::Base
  belongs_to :user

  def self.same_prefix_of(relation)
    Telephone.inner_join(relation.as('compared'),
      'SUBSTR(compared.number, 0, 5) = SUBSTR(telephones.number, 0, 5)')
  end

  def self.inner_join(table, condition)
    condition = condition.to_sql if condition.respond_to? :to_sql
    table = table.to_sql if table.respond_to? :to_sql
    join("INNER JOIN #{table} ON #{condition}")
  end
end

#Para usar:
Telephone.same_prefix_of(Telephone.where(id: 1))

Agora, para buscarmos o mesmo prefixo de algo, precisamos usar uma relation (logo, não podemos usar “first”). A vantagem desta abordagem é que podemos usar qualquer modelo para a busca, ou não necessariamente buscar apenas um prefixo, mas vários (dependendo da relation que fizermos). Por exemplo, com o código abaixo, é possível buscarmos todos os telefones com mesmo prefixo dos usuários de ID 1 e 2:

users = User.joins(:telephones).where(id: [1, 2]).select('telephones.number')
Telephone.same_prefix_of(users)

Nesse caso, entretanto, buscamos apenas os telefones que possuem o mesmo prefixo que o relation que passamos, mas infelizmente não sabemos qual telefone que foi retornado pertence a qual registro do relation. Para isso, podemos usar um “GROUP BY” no SQL, e deixar nosso código ainda mais genérico:

class Telephone < ActiveRecord::Base
  belongs_to :user

  def self.same_prefix_of(relation, *fields)
    phones = Telephone.inner_join(relation.as('compared'),
      'SUBSTR(compared.number, 0, 5) = SUBSTR(telephones.number, 0, 5)')
    return phones if fields.empty?

    fields.map! { |f| "compared.#{f}" }
    phones.select('telephones.*').select(fields)
          .group('telephones.id').group(fields)
  end

  def self.inner_join(table, condition)
    condition = condition.to_sql if condition.respond_to? :to_sql
    table = table.to_sql if table.respond_to? :to_sql
    joins("INNER JOIN #{table} ON #{condition}")
  end
end

O código, agora, ficou um pouco mais complexo. Até a linha 8, tudo é igual ao que fizemos antes. Depois, temos duas linhas adicionais: um “map!”, e um select com group. A idéia, aqui, é que caso tenhamos um campo adicional “fields”, ele nos trará os campos que queremos agrupar os resultados. A cláusula “group” deve trazer todos os campos que vamos agrupar, no caso “telephones.id” (chave primária) e os campos adicionais que pedimos. E aqui vale uma pequena observação sobre o “GROUP BY”:

O Group By, no SQLite3 e no MySQL é repleto de bugs: ele permite, por exemplo, fazer um agrupamento assim: “SELECT name, age, COUNT(name) FROM people GROUP BY name”. Só de olhar para essa query dá pra ver que temos algo de errado: queremos a contagem de pessoas que possuem o mesmo nome. O que a cláusula SELECT nos pede, também, é uma idade (e não temos como saber qual idade que eu quero retornar. Será a média das idades das pessoas com o mesmo nome? A idade máxima? A mínima?). No MySQL, ele pega uma idade aleatória, no SQLite3, ele faz uma bagunça maior (evita apresentar registros, etc). Já no PostgreSQL, essa cláusula dá um erro, como era de se esperar, falando que o “age” deve aparecer ou no group by ou numa função de agrupamento (tal como MAX, MIN, COUNT, AVG, etc).

O PostgreSQL, porém, é menos rigoroso (e mais inteligente) com o GROUP BY: se eu agrupar por um campo e há outros campos que dependem dele (tipo chave primária: todos os campos de uma mesma tabela dependem de sua chave primária) ele deixa o código “SELECT table.* FROM table GROUP BY table.id”. Já o Oracle é mais chato: ele pede que TODOS os registros que aparecerão no SELECT e que não estejam numa função de agrupamento devem aparecer no GROUP BY. Logo, nossa query acima não funcionaria, deveríamos de alguma forma puxar todos os campos da tabela “telephones”, e colocá-los no GROUP BY.

Para usar o código acima, é bem fácil também:

users = User.where(id: [1, 3]).joins(:telephones)
            .select('users.id id_of_users, telephones.number')
telephones = Telephone.same_prefix_of(users, 'id_of_users')

Isso irá trazer uma lista de todos os telefones cujos prefixos são iguais aos telefones dos usuários que passamos. Porém, ele também traz um campo que indica qual telefone possui prefixo igual a cada usuário:

telephones.map(&:attributes)
#=> [
# {"id"=>1, "number"=>"5555-4010", "user_id"=>1, "id_of_users"=>1},
# {"id"=>2, "number"=>"5555-1010", "user_id"=>2, "id_of_users"=>1},
# {"id"=>3, "number"=>"4040-1010", "user_id"=>3, "id_of_users"=>3},
# {"id"=>5, "number"=>"4040-6070", "user_id"=>nil, "id_of_users"=>3}
#]

Agrupando estas técnicas acima e outras técnicas, é possível fazer nosso ORM criar verdadeiras queries monstruosas com fragmentos de queries em nosso código. Agrupando com escopos, cláusulas WHERE, e mesmo repassando esses relations para outros relations, fica muito fácil montar queries simples, mas que podem ser combinadas de forma a formar queries complexas sem usar N+1, sem usar o Ruby/Rails para agrupar os resultados… e sim, é MUITO mais rápido.