MySQL: LEFT JOIN com GROUP BY não retorna os dados corretamente
Veja neste post como trabalhar corretamente com LEFT JOIN + GROUP BY. Para exemplificar usaremos 2 tabelas, a primeira que conterá os dados dos usuários e a segunda com os dados de acesso dos usuários.
Necessidade: trazer todos os usuários da Tabela usuarios, independente, de existir na Tabela usuarios_acesso, mais a data do último acesso.
Funções PHP:
LEFT JOIN: Retorna todas as linhas da tabela à esquerda, mesmo se não houver nenhuma correspondência na tabela à direita.
IFNULL: Retorna um valor alternativo se uma expressão for NULL.
Tabela 1: usuarios
+----------+-------------+ | Campo | Tipo | +----------+-------------+ | id | int(11) | | login | varchar(40) | | nome | varchar(200)| +----------+-------------+ mysql> SELECT * FROM usuarios; +----------+---------+---------------+ | id | login | nome | +----------+---------+---------------+ | 1 | login1 | Fulano | | 2 | login2 | Ciclano | | 3 | login3 | Beltrano | +----------+---------+---------------+ |
Tabela 2: usuarios_acesso
+----------------+--------------+ | Campo | Tipo | +----------------+--------------+ | id | int(11) | | login | varchar(40) | | data_conexao | DATETIME | +----------------+--------------+ mysql> SELECT * FROM usuarios_acesso; +----------------+-----------+---------------------+ | id | login | data_conexao | +----------------+-----------+---------------------+ | 1 | login1 | 2017-11-20 16:26:38 | | 2 | login1 | 2017-11-20 18:26:38 | | 3 | login1 | 2017-11-20 19:26:38 | | 4 | login2 | 2017-11-22 16:26:38 | | 5 | login2 | 2017-11-21 16:26:38 | +----------------+-----------+---------------------+ |
Executando o SELECT abaixo temos um pequeno problema. A coluna data_conexao, sempre, exibirá a data do primeiro registro encontrado. O ORDER BY para data_conexao não funciona.
mysql> SELECT tab1.nome, tab1.login, IFNULL(tab2.data_conexao, "não conectou ainda") as data_conexao FROM usuarios tab1 LEFT JOIN usuarios_acesso tab2 ON tab2.login = tab1.login GROUP BY tab2.login ORDER BY tab1.nome, tab2.data_conexao DESC; +----------+--------+---------------------+ | nome | login | data_conexao | +----------+--------+---------------------+ | Beltrano | login3 | não conectou ainda | | Ciclano | login2 | 2017-11-22 16:26:38 | | Fulano | login1 | 2017-11-20 16:26:38 | +----------+--------+---------------------+ |
Vamos tentar, agora, usando uma subconsulta.
Se executar o SELECT abaixo, mesmo que agrupe por login a coluna data_conexao, não é ordenada. Mais uma vez o ORDER BY não funciona.
mysql> SELECT tab1.nome, tab1.login, IFNULL(tab2.data_conexao, "não conectou ainda") as data_conexao FROM usuarios tab1 LEFT JOIN (SELECT login, data_conexao as data_conexao FROM usuarios_acesso GROUP BY login ORDER BY data_conexao DESC) tab2 ON tab2.login = tab1.login GROUP BY tab2.login ORDER BY tab1.nome, tab2.data_conexao DESC; |
Vamos mudar um pouquinho nosso SELECT; com apenas uma função de agrupamento resolvemos nosso problema e com isso, trazemos a data da última conexão e não a primeira, como nos SELECTS anteriores.
O GROUP BY é usado com funções de agrupamento (COUNT, MAX, MIN, SUM, AVG) para agrupar o resultado de 1 ou mais colunas, e não para ordenar.
Para funcionar você deve usar a função de agrupamento MAX() para retornar a data da última conexão, agrupada por login – neste caso não precisa do ORDER BY na subconsulta.
Veja o SELECT completo.
mysql> SELECT tab1.nome, tab1.login, IFNULL(tab2.data_conexao, "não conectou ainda") as ultima_conexao FROM usuarios tab1 LEFT JOIN ( SELECT login, MAX(data_conexao) as data_conexao FROM usuarios_acesso GROUP BY login ) tab2 ON tab1.login = tab2.login ORDER BY tab1.nome; +----------+--------+---------------------+ | nome | login | ultima_conexao | +----------+--------+---------------------+ | Beltrano | login3 | não conectou ainda | | Ciclano | login2 | 2017-11-22 16:26:38 | | Fulano | login1 | 2017-11-20 18:26:38 | +----------+--------+---------------------+ |
Forte abraço!