JOIN limitar registros da tabela filho por cada linha da tabela pai

[Didyo 1]

Olá pessoal, gostaria de saber se é possível e como limitar a quantidade de linha retornadas usando JOIN.
A ideia é limitar a quantidade de linhas retornadas da tabela filho em relação a cada linha da tabela pai.

Ex. Tenho 10 registros de imóveis na tabela_imv e cada um destes possui mais de 12 imagens na tabela_imgs (cada imagem em uma linha diferente).

Usando o LEFT JOIN gostaria que para cada imóvel fossem trazidas apenas 5 imagens usando LIMIT 5 de modo que a cada 5 imagens encontradas, a busca cessasse na tabela filho, e retomasse procurando as 5 primeiras imagens do próximo imóvel.

Desde já agradeço a atenção.

[Didyo 1]

Olá pessoal, 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 é reconhecida. 

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 parece não ter ficado setado, pois não retornou registros.

 

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 seria o mesmo que inserir manualmente o id dentro do SELECT contido no JOIN.

 

Alguém tem alguma ideia de como resolver?

Obrigado

[Didyo 1]

Bem pessoal consegui uma solução simples, que não é exatamente o que procurava,
mas dá para quebrar um galho.
Vou continuar tentando encontrar a solução para a forma original que queria, usando (LIMIT) dentro do JOIN.

 

Objetivo:
Uso de 2 tabelas relacionadas 1:n onde temos vários imóveis na primeira tabela e cada imóvel possui várias imagens em outra tabela. 
Objetivo: Retornar apenas 5 imagens para cada imóvel em um SELECT com JOIN.

 

Solução provisória:
Ter na tabela imagens, além dos campos como (id, fk_id_imovel, nome_img, diretorio),
um campo ordem_imgs que deverá ser populado com números sequências iniciando no 1.

Assim no SELECT ficaria algo do tipo:

 

SELECT i.*, img.* FROM imoveis i 

LEFT JOIN imagens img ON img.fk_id_imovel=i.id AND img.ordem_imgs <=5 

 

Só para base de conhecimento:
Pode-se usar uma biblioteca JQuery  como (sortable) junto com o PHP e MySql para se ordenar as imagens arrastando-as (DRAG and Drop) no lado do cliente de modo a atualizar a sequência de números que determina a ordem das imagens na tabela do banco de dados.

[Bergs 53]

Essa situação poderia ser escrita sem JOIN mas apenas com a cláusula WHERE certo.

 

LIMIT não pode ser usado com JOIN.

[Didyo 1]

3 horas atrás, Bergs disse:

Essa situação poderia ser escrita sem JOIN mas apenas com a cláusula WHERE certo.

 

LIMIT não pode ser usado com JOIN.

 

Olá Bergs, realmente poderia usar o WHERE no lugar de  AND img.ordem_imgs <=5  

Ficaria: 

SELECT i.*, img.* FROM imoveis i 

LEFT JOIN imagens img ON img.fk_id_imovel=i.id 
WHERE img.ordem_imgs <=5 

 

O JOIN só permaneceria obviamente para o relacionamento das tabelas.

No caso eu dei preferencia para aproveitar o ON ao invés de usar o WHERE 

 

Quanto a LIMIT não poder ser usado com JOIN
como você disse,
eu estou cada vez mais ciente de que possa ser impossível mesmo.

 

Acredito que por não ter uma maneira obvia de conseguir usar o LIMIT com JOIN, 
deve ser por não haver necessidade para isso,
afinal os DBs são bem elaborados e quando usamos chaves estrangeiras, índices,... 
imagino que o mysql já deva saber  que
o imóvel id=35 possua 12 imagens na tabela
ou que possua 5 imagens com com o campo (ordem_imgs) <=5.
Isso por causa do uso do índice, senão ele teria que 
verificar todos registros um a um para calcular isso.
Dessa forma, por já saber, é provável que ele já use o LIMIT automaticamente.

 

Suponho que é como se o mysql dissesse:  

 

"no meu índice indica 12 imagens para o id 35, encontrei elas, então encerro a busca por mais imagens com esse id e prossigo procurando as imagens relacionadas aos outros ids."

 

"no meu índice indica 5 imagens para o id 35 com o campo (ordem_imgs) tendo valor <=5, encontrei elas,
então encerro a busca por mais imagens com esse id e prossigo procurando as imagens relacionadas aos outros ids."

 

Não teria o porque após encontrar as 5 ou 12 imagens desejadas,
prosseguir verificando se encontra mais imagens
nas outras centenas ou milhares de linhas restantes na tabela.

 

Outra evidência é que ele tenha esse entendimento de quantas imagens existam relacionadas com determinado id,
é que quando usamos ON DELETE RESTRICT ele instantaneamente não permite deletar o registros que tenha outros relacionados. Será que ele varre toda a tabela para saber se tem tais registros (o que seria mais lento) ou usa os índices? Não tenho dúvidas que ele usará os índices.
 

Ou seja, o mysql é poderoso e inteligente 
e quando usamos chaves estrangeiras e índices,
ele organizará e usará os registros da melhor forma possível. 

 

[Bergs 53]

1 hora atrás, Didyo disse:

 

Olá Bergs, realmente poderia usar o WHERE no lugar de  AND img.ordem_imgs <=5  

Ficaria: 

SELECT i.*, img.* FROM imoveis i 

LEFT JOIN imagens img ON img.fk_id_imovel=i.id 
WHERE img.ordem_imgs <=5 

 

O JOIN só permaneceria obviamente para o relacionamento das tabelas.

No caso eu dei preferencia para aproveitar o ON ao invés de usar o WHERE 

 

Quanto a LIMIT não poder ser usado com JOIN
como você disse,
eu estou cada vez mais ciente de que possa ser impossível mesmo.

 

Acredito que por não ter uma maneira obvia de conseguir usar o LIMIT com JOIN, 
deve ser por não haver necessidade para isso,
afinal os DBs são bem elaborados e quando usamos chaves estrangeiras, índices,... 
imagino que o mysql já deva saber  que
o imóvel id=35 possua 12 imagens na tabela
ou que possua 5 imagens com com o campo (ordem_imgs) <=5.
Isso por causa do uso do índice, senão ele teria que 
verificar todos registros um a um para calcular isso.
Dessa forma, por já saber, é provável que ele já use o LIMIT automaticamente.

 

Suponho que é como se o mysql dissesse:  

 

"no meu índice indica 12 imagens para o id 35, encontrei elas, então encerro a busca por mais imagens com esse id e prossigo procurando as imagens relacionadas aos outros ids."

 

"no meu índice indica 5 imagens para o id 35 com o campo (ordem_imgs) tendo valor <=5, encontrei elas,
então encerro a busca por mais imagens com esse id e prossigo procurando as imagens relacionadas aos outros ids."

 

Não teria o porque após encontrar as 5 ou 12 imagens desejadas,
prosseguir verificando se encontra mais imagens
nas outras centenas ou milhares de linhas restantes na tabela.

 

Outra evidência é que ele tenha esse entendimento de quantas imagens existam relacionadas com determinado id,
é que quando usamos ON DELETE RESTRICT ele instantaneamente não permite deletar o registros que tenha outros relacionados. Será que ele varre toda a tabela para saber se tem tais registros (o que seria mais lento) ou usa os índices? Não tenho dúvidas que ele usará os índices.
 

Ou seja, o mysql é poderoso e inteligente 
e quando usamos chaves estrangeiras e índices,
ele organizará e usará os registros da melhor forma possível. 

 

 

Isso mesmo Join para relacionamentos, acabei confundindo. 

 

Você tem essas tabelas  e como printar elas para entender melhor o uso do operador relacional no campo ordem_imgs <=5  porque não entendi a questão do filtro pela ordem. Seria um campo do tipo Primary Key com AUTO_INCREMENT   e por ele você fez o limite?

 

 

 

 

 

 

 

 

 

[Didyo 1]

6 horas atrás, Bergs disse:

Isso mesmo Join para relacionamentos, acabei confundindo. 

Você tem essas tabelas  e como printar elas para entender melhor o uso do operador relacional no campo ordem_imgs <=5  porque não entendi a questão do filtro pela ordem. Seria um campo do tipo Primary Key com AUTO_INCREMENT   e por ele você fez o limite?

 

Olá Bergs, fiz uma tabela simples apenas para demonstração. Segue em anexo uma imagem.

Quanto ao campo ordem_imgs não é do tipo Primary Key e nem AUTO_INCREMENT.

Tanto é que todos imóveis terão as imagens com a mesma numeração em sua ordem (1,2,3,4,...) havendo números repetidos, mas não para um mesmo imóvel.

 

Tem uma explicação junto a imagem.

Algo que poderia usar seria um Índice UNIQUE com os campos fk_imoveis_imv_id e img_ordem. Assim o DB não permitirá que determinado id de imóvel tenha números repetidos no campo img_ordem. 

 

Abraço e dúvidas ou sugestões não deixe de compartilhar.

 

[Bergs 53]

Olá Didyo muito massa.  Obrigado!