MySQL limitar quantidade de linhas em um JOIN para cada linha da tabela pai

Banco de Dados
#1

Boa noite a todos,
Gostaria de uma ajuda para usar LIMIT dentro de um JOIN.

Exemplo: 2 tabelas relacionadas (empresas e imagens)
Cada imóvel possui n imagens, porém no SELECT com JOIN gostaria que retornassem apenas 5 imagens para cada empresa.
Tive pensando em resolver a questão com algo tipo

SELECT c.cat_id as id, c.cat_nome_fantasia as empresa, i.img_nome as imagem FROM catalogo c
LEFT JOIN (SELECT * FROM imagens WHERE fk_catalogo_id=c.cat_id LIMIT 3) i ON c.cat_id=i.fk_catalogo_id

Porém a coluna cat_id na subquery (WHERE fk_catalogo_id=c.cat_id ) não é encontrada.

Sabem se existe alguma forma de mudar isso? Tipo tornar a coluna Global.

Então tentei

SELECT (@valor_id:=c.cat_id) as id, c.cat_nome_fantasia as empresa, i.img_nome as imagem FROM catalogo c
LEFT JOIN (SELECT * FROM imagens WHERE fk_catalogo_id=@valor_id LIMIT 3) i ON c.cat_id=i.fk_catalogo_id

Porém o @valor_id também não é reconhecido dentro do JOIN, ficando com NULL.

Só para informação:
Fiz esse teste para ver se o @valor_id aparece com algum valor na subquery contida no JOIN:

SELECT (@valor_id:=c.cat_id) as id, c.cat_nome_fantasia as empresa, i.img_nome as imagem, valor_id FROM catalogo c
LEFT JOIN (SELECT im.*, @valor_id AS valor_id FROM imagens im ) i ON c.cat_id=i.fk_catalogo_id

Note que não usei o LIMIT.
Resultado: apenas como exemplo
id || empresa || imagem || valor_id
1 || emp1 || img1 || NULL

Por fim tentei setar a variável.

SET @valor_id=1;

SELECT (@valor_id:=c.cat_id) as id, c.cat_nome_fantasia as empresa, i.img_nome as imagem FROM catalogo c
LEFT JOIN (SELECT * FROM imagens WHERE fk_catalogo_id=@valor_id LIMIT 3) i ON c.cat_id=i.fk_catalogo_id

Nesse caso quase funcionou, buscou as imagens com LIMIT,

mas, ficou preso a setar manualmente o @valor_id, ou seja, só busca as imagens do id setado, o que não teria serventia, pois poderia inserir o id direto no SELECT contido no JOIN.

Alguém tem alguma ideia de como resolver?

Obrigado

#2

Tenta assim, só que faz o teste direto no seu Gerenciador de Banco de dados Não no ScriptCase, testando e vendo, que
deu certo, crie uma View e use a view no scriptcase, porque o SC se você usar a palavra LIMIT na SQL ele bagunça tudo, ai
nunca daria certo diretamente, por isso que tem que usar neste caso a VIEW como macete.
SELECT
catalogo.cat_id,
catalogo.cat_nome_fantasia as empresa,
imagens.img_nome as imagem
FROM
catalogo
LEFT JOIN (SELECT * FROM imagens WHERE imagens.fk_catalogo_id=catalogo.cat_id LIMIT 5) ON catalogo.cat_id=imagens.fk_catalogo_id

#3

faça uma procedure para popular uma tabela Auxiliar com os dados requiridos
abrir cursor por empresas e dentro por cada empresa um LIMIT 5 de imagens

#4

Olá Jailton, agradeço a sugestão.
Fiz como mencionado mas não deu certo.
Apresentou o seguinte erro:

#1248 - Every derived table must have its own alias

Então tentei modificando para:
SELECT
catalogo.cat_id,
catalogo.cat_nome_fantasia as empresa,
i.img_nome as imagem
FROM
catalogo
LEFT JOIN (SELECT * FROM imagens WHERE imagens.fk_catalogo_id=catalogo.cat_id LIMIT 5) i ON catalogo.cat_id=i.fk_catalogo_id LIMIT 0, 25

e volta ao erro: coluna (catalogo.cat_id) não encontrada.

Note que o erro sempre dá no SELECT após o LEFT JOIN.
Fora, como após o ON, a coluna sempre é reconhecida.

#5

Tenta assim que vai dar certo:
SELECT
imagens.fk_catalogo_id AS ID,
catalogo.cat_nome_fantasia AS Empresa,
imagens.img_nome AS Imagem,
COUNT()
FROM
imagens
INNER JOIN catalogo ON imagens.fk_catalogo_id=catalogo.cat_id
GROUP BY
imagens.fk_catalogo_id HAVING(COUNT())>0 AND HAVING(COUNT(*))<=5

  • Tem que observar as vezes usar no GROUP BY o código da empresa/cliente não sei se é o campo ‘catalogo.cad_id’ que esta amarrado as imagens,
    ai ele vai contar quantas imagens tem e pegar a quantidade desejada no HAVING com COUNT.
SELECT
	imagens.fk_catalogo_id AS ID,
	catalogo.cat_nome_fantasia AS Empresa,
	imagens.img_nome AS Imagem,
	COUNT(*)
FROM
    imagens    
    INNER JOIN catalogo ON imagens.fk_catalogo_id=catalogo.cat_id
GROUP BY 
    imagens.fk_catalogo_id HAVING(COUNT(*))>0 AND HAVING(COUNT(*))<=5
#6

Olá Jailton, mais uma vez obrigado pela colaboração.
Fiz conforme citado, com uma leve correção no final do código pois o HAVING estava duplicado, dando erro.
O final ficou: GROUP BY imagens.fk_catalogo_id HAVING(COUNT())>0 AND (COUNT())<=5

No entanto o [b]HAVING /b parece similar ao [b]WHERE /b.
Ou seja, me trouxe apenas resultados com 5 imagens ou menos.
Tendo 6 imagens ou mais, não foram retornados.

#7

Olá Willian, agradeço a colaboração.
Quando mencionou procedure para popular uma tabela auxiliar, seria o mesmo que usar tabela temporária?
A ideia seria gerar uma consulta e armazenar apenas 5 imagens por empresa em uma tabela temporária?

Aproveitando, estava pensando nessa situação, levando em consideração o desempenho.

Me veio em mente que os DBs e seus motores são bem elaborados para fazerem as melhores escolhas nas consultas, que índices usar e quando, …

Então por não ter uma opção óbvia (simples, fácil) de implementar LIMIT aplicado um JOIN,
comecei a acreditar que é por não haver necessidade.

Em outras palavras, suponho que, ao trabalhar com chaves estrangeiras, índices,…
na organização dos dados o DB já guarda informações tipo o ID 1 possui 5 ocorrências na tabela relacionada, o ID 3 possui 12 ocorrências,…

Assim ele já saberia que no JOIN ao encontrar 5 linhas do ID 1 ele deve parar de procurar o ID 1
e prosseguir até encontrar as 12 ocorrências do ID 3
e assim sucessivamente.

Essa suposição lhe faz sentido?

#8

Pode-se tentar colocar as imagens em sequência, de cadastro, criando um campo novo na tabela imagens campo: SequenciaImagem INT(11).

E usando a rotina no seu gerenciador de base do MySQL pode-se usar o HediSQL, depois que testar ela pode-se criar uma procedure para chamar direto futuramente:

SET @SequenciaImagem:=10000000;
SET @fk_catalogo_id:=0;
UPDATE
imagens
SET
SequenciaImagem=
IF(CONCAT(@fk_catalogo_id)<>CONCAT(imagens.fk_catalogo_id), ABS(SUBSTR(CONCAT(@SequenciaImagem:=10000001, @fk_catalogo_id:=imagens.fk_catalogo_id),2,7)),
ABS(SUBSTR(CONCAT(@SequenciaImagem:=@SequenciaImagem+1),2,7)));

/----------/----------------/------------------

Com as imagens em sequência ai depois só fazer uma simples Query SQL que já daria certo:
SELECT
imagens.fk_catalogo_id AS ID,
catalogo.cat_nome_fantasia AS Empresa,
imagens.img_nome AS Imagem
FROM
imagens
INNER JOIN catalogo ON imagens.fk_catalogo_id=catalogo.cat_id
WHERE
imagens.SequenciaImagem<=5