De uns tempos para cá, tenho vistos muitos códigos bizarros acontecendo no mundo dos ORMs, que eventualmente me deixaram pensando: será que ORMs estão fazendo-nos esquecer como se faz SQLs?
No mundo Ruby/Rails, virou quase uma regra não-escrita que escrever SQLs na mão é errado. Quanto mais pudermos aproveitar o ActiveRecord (e algumas mágicas que ele faz por nós), melhor. Isso acaba trazendo algumas coisas bem esquisitas, tal como achar que 90% dos códigos nunca vão precisar de “OR” (e maior, menor, diferente, etc), já que a API do ActiveRecord não suporta isso, ou algumas outras práticas meio estranhas. Do outro lado, temos por exemplo o framework “Play!”, de Scala, que faz o extremo oposto: não usa nenhum ORM, e todas as queries SQL passam a ser feitas na mão…
O ponto é que quando temos um ORM, parece que abusamos dele e esquecemos que nem tudo é um “SELECT * FROM <table> WHERE <attribute> = <value>”. Por exemplo, no código abaixo:
users = User.all
users.each do |user|
puts "User #{user.name} has #{user.addresses.count} addresses"
end
Temos o famoso caso do “N+1”: 1 busca para achar N usuários, e a partir daí “N” buscas para achar a contagem de endereços. O problema é que muita gente acha que apenas um “User.include(:addresses)” resolve o problema, quando na verdade não resolve: o “include” vai trazer todos os endereços, mas a contagem (se o Rails optar por usar “count”) vai continuar sendo feita em banco, ou então (se o Rails optar por não usar “count”) será feita em Ruby, e teremos trazido registros a mais do banco de dados sem necessidade. A solução, nesse caso, é usar um comando SQL mesmo:
users = User.all
num_addresses = Address.join(:user).group('user.id').count
users.each do |user|
puts "User #{user.name} has #{num_addresses[user.id]} addresses"
end
Ok, mas SQL é difícil, chato, e é mais fácil fazer as coisas em Ruby, certo? Bom, sim e não. É mais fácil fazer os comandos em Ruby (ou na linguagem que você escolher) puramente por “costume”. Estamos acostumados a fazer a sequencia: buscamos uma lista, tratamos a lista, exibimos a lista. SQL não trabalha com “listas”, mas com “conjuntos”. E é isso que vamos ver a seguir:
Para os exemplos a seguir, vamos pensar em três tabelas: “users”, “addresses”, e uma “join table” chamada “residents”. Todas as tabelas possuem uma chave primária (chamada “id”) e residents possui chaves estrangeiras (“user_id” e “address_id”). Quando eu mando um comando como:
SELECT * FROM users WHERE users.name = 'Foo'
eu NÃO estou buscando uma “lista” de usuários: a “lista” é a forma como o resultado vem para mim. Na busca, eu estou trabalhando em cima de um conjunto:
No conjunto (A), temos todos os registros de minha tabela users, e neste conjunto e defino um subconjunto (R) (com o where) aonde a característica comum a todos os elementos é que o nome é “Foo” (nota: estou usando “R” para o conjunto dos “resultados”. Não confundir com o conjunto dos números REAIS)
SELECT name, COUNT(name) FROM users GROUP BY name HAVING COUNT(name) > 1
Neste caso, nosso comando SQL cria um subconjunto (B) usando o group by. Nesse subconjunto, os elementos são agrupados pelo nome, isto é, os nomes iguais são jogados no mesmo elemento no conjunto B. Claro que outros atributos como “id” passam a não fazer mais sentido, pois no agrupamento há mais de um “id”, por exemplo. Depois de criado esse segundo subconjunto, então podemos fazer filtros em cima dele. Para fazer um filtro sobre o segundo subconjunto, usamos a cláusula HAVING. Por fim, “filtramos” esse subconjunto e temos nossos resultados.
SELECT * FROM users u1, users u2
Caso estejamos usando duas tabelas na cláusula “FROM”, nosso conjunto é simplesmente a UNIÃO dos dois. Note que isso vai fazer todos os elementos ficarem duplicados. Note que isso vale também se usarmos, na cláusula FROM, um subselect (com alias), ou se fizermos a mesma coisa num JOIN (seja ele LEFT, RIGHT ou INNER JOIN). A diferença do JOIN é que precisamos, no JOIN, de uma condição para unir a tabela do JOIN com o FROM.
SELECT u1.id, u2.id FROM users u1, users u2
Nesse caso, usamos um “SELECT” específico. Assim como no exemplo anterior, estamos puxando registros da mesma tabela duas vezes, porém dessa vez eu peço pelo ID de uma e o ID da outra. Como combinar isso? Aqui, o SQL começa a ficar um pouco mais complicado:
A operação a ser feita é o “produto cartesiano”. Produto Cartesiano nada mais é do que todas as combinações possíveis do conjunto 1 com o conjunto 2. Como queremos apenas o ID, o resultado vai ser o produto cartesiano dos IDs da tabela “users” com os IDs da tabela “users”. O conjunto de resultado (R) está incompleto no desenho porque temos MUITAS possibilidades: a lista de resultados é “1-1”, “1-2”, “1-3”, “1-4”, “1-5”, “2-1”, “2-2”, “2-3”, “2-4”, “2-5”, “3-1”, “3-2”, “3-3”, “3-4”, “3-5”, “4-1”, “4-2”, “4-3”, “4-4”, “4-5”, “5-1”, “5-2”, “5-3”, “5-4”, “5-5”. Repare que NÃO TEMOS elementos duplicados nesse exemplo.
Ok, mas só o produto cartesiano não serve para nada. Seria legal, por exemplo, sabermos quais pessoas possuem homônimo (mesmo nome) de alguém. Para sabermos se alguém tem o mesmo nome, precisamos das seguintes operações no conjunto: de todas as pessoas, quero buscar quem tem o mesmo nome que eu, mas não sou eu:
SELECT all_users.id, all_users.name, same_names.id FROM users all_users, users same_names WHERE all_users.name = same_names.name AND all_users.id <> same_names.id
Talvez este tenha ficado um pouco difícil de ler: basicamente, o SQL faz o produto cartesiano novamente dos dois conjuntos (A e B), exceto que desta vez há regras para fazê-lo: fazemos apenas se o que foi definido na cláusula WHERE for atendido (ou seja, se o “nome” for igual nos dois conjuntos e se o “id” for diferente nos dois conjuntos).
Bom, agora para um caso bem mais complexo: vamos tentar montar uma SQL que tente encontrar usuários que possuem todos o endereços em comum: para fazer este tipo de busca, precisamos pensar numa operação em conjuntos que forme o conjunto que queremos. Então, antes disso, vamos pensar em cláusulas WHERE de uma forma diferente de como temos pensado até agora:
SELECT * FROM users WHERE users.name = 'Foo' OR users.name = 'Bar'
Para essa query, vamos pensar em três conjuntos: o conjunto (A) é o conjunto de todos os registros na tabela “users”. O conjunto (B) é o conjunto de TODOS os registros cujo nome é ‘Foo’. Repare que este conjunto não está limitado pela tabela que estamos buscando, então considere que ele é um conjunto hipotético: todos os registros que existem ou que podem existir, no mundo, cujo nome é ‘Foo’. O conjunto (C) é igual ao conjunto (B), porém cujo nome é ‘Bar’.
O conjunto (R) é o resultado da UNIÃO de conjuntos (X) e (Y), onde on conjunto (X) é a INTERSECÇÃO dos conjuntos (A) é (B), e (Y) é a INTERSECÇÃO dos conjuntos (A) é (C). Logo, a idéia é tentar criar conjuntos aonde podemos fazer intersecções, uniões, produtos cartesianos, etc. Então, indo passo-a-passo, vamos tentar primeiro achar todos os endereços de uma pessoa:
SELECT * FROM users INNER JOIN residents ON residents.user_id = user.id INNER JOIN addresses ON address.id = residents.address_id
Não vamos entrar em “JOIN” ou desenhar o conjunto para este caso porque é muito simples. A idéia é que essa busca já me traz todas as pessoas e seus respectivos endereços. Obviamente, se uma pessoa possui mais de um endereço, ela vai aparecer na tabela mais de uma vez. Para simplificar os próximos exemplos, ao invés de escrever todos estes “INNER JOIN” vou apenas escrever “ALLJOINS” (nota: abreviações assim não existem no SQL), e no fim deste post mostrarei a query completa. Bom, a primeira coisa que precisamos fazer é identificar TODOS os usuários que possuem ao menos UM endereço igual a outro usuário. Isso é relativamente simples (usando os exemplos que já vimos antes):
SELECT u1.id, u1.address_id, u2.id AS other_id FROM (SELECT users.id, addresses.id AS address_id FROM users ALLJOINS) u1, (SELECT users.id, addresses.id AS address_id FROM users ALLJOINS) u2 WHERE u1.address_id = u2.address_id AND u1.id <> u2.id
Criamos dois conjuntos (no FROM) exatamente iguais (exceto pelos nomes dos atributos), e fazemos uma intersecção entre eles. A regra para a intersecção é que o “addresses.id” de ambos devem ser iguais, mas o “user.id” deles deve ser diferente (porque queremos saber quem mora no mesmo endereço que determinado usuário, e obviamente o usuário “1” mora no mesmo endereço de si mesmo. Então, excluímos o “si mesmo” da lista). Isso dá um conjunto como os abaixo:
Esse conjunto é BEM complicado, então vamos lá: (A) é o conjunto da primeira query do FROM, (B) é o conjunto da segunda query. (A x B) é o conjunto formato pelo produto cartesiano de (A) com (B). Há um conjunto (Y) com TODOS os elementos que tem “address_id” iguais no produto cartesiano, e outro (X) com TODOS os elementos que tem “id” diferentes no produto cartesiano. Estes dois conjuntos estão interseccionando o conjunto (A x B), e a resposta de nossa query, o conjunto (R), é a intersecção entre (X) e (Y).
Já temos a lista de todos os usuários. Então, precisamos encontrar uma regra que permita fazer intersecção entre os dois conjuntos e que me dê o resultado que eu quero. Vamos olhar para os resultados um pouco:
ENDEREÇOS DOS USUARIOS id | address_id ---+----------- 1 | 1 1 | 2 2 | 1 3 | 3 4 | 1 4 | 2 USUARIOS COM PELO MENOS 1 ENDEREÇO EM COMUM id | address_id | other_id ---+------------+---------- 1 | 1 | 2 1 | 1 | 4 1 | 2 | 4 2 | 1 | 1 2 | 1 | 4 4 | 1 | 1 4 | 1 | 2 4 | 2 | 1
Pelo primeira tabela é fácil de identificar que quem morou nos mesmos endereços que o usuário de id 1 foi o usuário de id 4. Repare que na segunda tabela, o usuário 4 aparece, junto com o usuário 1, duas vezes. Vamos pensar de forma indexada: na primeira tabela (indexado pelo “id”), o usuário (1) aparece duas vezes. Na tabela (2) (indexado por “id” e “other_id”, isto é, indexado pelo usuário atual e pelo usuário que estamos fazendo a intersecção), o usuário (1, 4) também aparece duas vezes. Sabemos que nessa segunda tabela, existem APENAS endereços que os dois usuários possuem (tanto é que ela é simétrica – se um registro (1, 2, 4) aparece, com certeza aparecerá um (4, 2, 1) também), logo podemos concluir que se um usuário tem “2” endereços na primeira tabela e tem “2” endereços junto com outro usuário na segunda, então ambos possuem os mesmos endereços. Ou seja, nas consultas acima, podemos fazer uma CONTAGEM e então teremos um atributo para fazer nossa intersecção:
SELECT user_with_same_address.id, user_with_same_address.other_id
FROM
-- A query abaixo é igual à anterior, porém com a contagem
(
SELECT u1.id AS id, u2.id AS other_id, COUNT(u1.address_id) AS count
FROM
(SELECT people.id, user_addresses.address_id AS address_id FROM people ALLJOINS) u1,
(SELECT people.id, user_addresses.address_id AS address_id FROM people ALLJOINS) u2
WHERE u1.address_id = u2.address_id AND u1.id <> u2.id
GROUP BY u1.id, u2.id
) user_with_same_address, (
-- A query abaixo é a contagem de endereços de um usuário
SELECT people.id, COUNT(user_addresses.address_id) AS count
FROM people ALLJOINS
GROUP BY people.id
) all_addresses
WHERE all_addresses.count = user_with_same_address.count
AND all_addresses.id = user_with_same_address.other_id
A query acima traz dois registros, ambos IDs de usuário: o primeiro é o ID do usuário que contém todos os endereços do usuário definido pelo segundo ID. Claro que nesse caso específico desta query, como estamos procurando apenas pelos IDs das tabelas, seria MUITO mais simples usar apenas a “joins table” chamada “residents”:
SELECT user_with_same_address.id, user_with_same_address.other_id FROM ( SELECT r1.user_id AS id, r2.user_id AS other_id, COUNT(r1.address_id) AS count FROM residents r1, residents r2 WHERE r1.address_id = r2.address_id AND r1.person_id <> r2.person_id GROUP BY r1.id, r2.id ) user_with_same_address, ( SELECT user_id, COUNT(address_id) AS count FROM residents ) all_addresses WHERE all_addresses.count = user_with_same_address.count AND all_addresses.id = user_with_same_address.other_id
De qualquer forma, qualquer uma destas duas queries tem o mesmo objetivo, e fazem a mesma operação de conjuntos: para este exemplo, vamos chamar de (A) o conjunto que foi gerado pela query “user_with_same_address”, sem o COUNT (basicamente o conjunto é o mesmo que foi apresentado anteriormente), e de (B) o conjunto que foi gerado pela query “user_with_same_address”, com o COUNT. De (C), o conjunto do “all_addresses”:
Talvez fique um pouco de ler, mas é só tentar interpretar a ordem aonde os elementos aparecem: Conjunto (A) -> user_id, address_id, other_id (ou u2.user_id). Conjunto (B): user_id, other_id, COUNT(address_id). Conjunto (C): user_id, COUNT(address_id). Isso vira um produto cartesiano, e temos por fim a intersecção das condições que fizemos: que a contagem de endereços do usuário seja a mesma que a contagem de endereços que são iguais aos de outro usuário, e que o usuário que estamos fazendo a contagem dos endereços seja o mesmo que o que estamos verificando se contém os mesmos endereços. Esta query gera um resultado igual ao seguinte:
id | other_id ---+--------- 1 | 4 1 | 2 4 | 1 4 | 2
Interpretando: O usuário “1” tem os mesmos endereços que o “2” e o “4”. O usuário “4” tem os mesmos endereços que o “1” e o “2”. O usuário “3” não tem os mesmos endereços que ninguém, e o “2” também não-ele só possui UM endereço dos usuários “1” e “4”. Obviamente, os usuários “1” e “4” possuem DOIS endereços, e coincidentemente um deles é o do usuário “2”-logo, eles aparecem na tabela como possuidores de todos os endereços do usuário “2”.
Num próximo POST, falaremos um pouco mais sobre ORMs, otimização de queries e como interpretar o resultado de um EXPLAIN. Como um exercício, é interessante tentar entender melhor o exemplo acima (dos conjuntos), e tentar visualizar quais elementos aparecem nos conjuntos (X) e (Y), acima.