Pesquisa em MySQL com dados relacionados

[Guilherme Luiz 41]

Olá pessoal,

Sei que tem diversos artigos que explicam como fazer consultas relacionadas numa única query, porem estou com dúvida na lógica da minha consulta... vamos lá.

Tenho um sistema onde existe um nível de acesso.

1 - Master

2 - Whitelabel

3 - Admin

4 - Subuser

5 - Afiliado

De acordo com este nível de acesso, quero criar um sistema da log de movimentação de saldo.

Seguindo essa ordem eu tenho a seguinte lógica:

O nível 1 visualiza toda a movimentação de todos os níveis, incluindo do próprio nível

O nível 2 visualiza tudo do nível 3, incluindo o próprio nível

O nível 3 visualiza tudo do nível 4, incluindo o próprio nível

O nível 4 visualiza somente sua movimentação.

Baseado nisso em minha tabela de log (que se chama saldo_log) eu terei uma coluna chamada de chave_especial_user para identificar de quem é aquele log.

Até aí tenho tudo ok pois consigo fazer com que cada um consiga visualizar o seu log.

Numa tabela chamada user_details eu tenho a coluna chave_especial_user para identificar de qual usuário são os detalhes cadastrados e também possuo uma coluna chamada chave_subuser que terá nela a chave_especial_user do usuário "chefe"

Por exemplo

Usuário com login nível 3 terá sua identificação na chave_especial_user e em chave_subuser terá a chave_especial_user de algum usuário nível 2

Portanto a relação da pesquisa, isso levando em consideração que eu seria um usuário nível 2 eu teria, talvez, a seguinte lógica de pesquisa:

Através da minha chave_especial_user (que está salva numa session) vou realizar uma pesquisa na tabela user_detail na coluna chave_subuser para puxar quais usuários possuem a minha chave_especial_user vinculada.

Tendo este resultado, ainda na tabela user_detail eu puxo a chave_especial_user destes meus sub-usuários para então consultar a tabela saldo_log através da coluna chave_especial_user.

E no resultado desta pesquisa, além de sair os meus logs, também terá que sair o log destes meus sub-usuarios numa única pesquisa.

É possível aplicar essa lógica numa única query com o INNER JOIN ou alguma outra técnica?

Não queria fazer do modo "tradicional" realizando uma pesquisa e a partir desta pesquisa, fazer outra pesquisa dentro do meu while... isso num determinado momento vai comer todo o processamento do meu BD.

[Williams Duarte 431]

É possível aplicar essa lógica numa única query com o INNER JOIN ou alguma outra técnica?

Mais fácil usar uma subquery para saber que tem chave_especial_user vinculada e ordenar os niveis, da pra usar os dois juntos, JOIN+SubQuery, join entra só para filtrar o usuario, mas não os niveis, apesar que da para filtrar junto ao operador ON

ON b.id_user = a.id AND b.nivel >= a.nivel

[Guilherme Luiz 41]

Fiz uma alteração nas minhas tabelas tendo o seguinte formato

Tabela > user_login

ID

login

chave_especial_user

Nivel (set de 1, 2, 3, 4 e 5)

Tabela > user_detail

ID

chave_especial_user

chave_especial_subuser

Tabela > saldo_log

ID

chave_especial_user

chave_especial_subuser

data

valor

servico

Qual a relação das tabelas:

Na tabela user_login é onde armazeno informações sobre a chave_especial daquele usuário e também o nível dele.

Na tabela user_detail é onde armazeno alguns detalhes do usuario.

Dentre vários detalhes que vou armazenar nela, eu armazeno a chave_especial do usuário a quem pertence aqueles dados e também armazeno a chave_especial_subuser do usuário de nível maior.

Onde a regra que tenho é a mesma citada no post anterior:

O nível 1 visualiza toda a movimentação de todos os níveis, incluindo do próprio nível e pode criar contas de qualquer nível

O nível 2 visualiza tudo do nível 3, incluindo o próprio nível e pode criar contas do nível 3

O nível 3 visualiza tudo do nível 4, incluindo o próprio nível e pode criar contas do nível 4

O nível 4 visualiza somente sua movimentação e não pode criar nenhum tipo de contas.

Na tabela saldo_log pensei em:

Todo log será vinculado ao usuário que gerar alguma movimentação que será identificado pela chave_especial_user e também será identificado a chave_especial_subuser do usuário de nível maior

Por exemplo:

Usuário nível 3 foi criado por um usuário de nível 2.

Usuario nível 3 loga no sistema.

Então numa session tenho armazenado a chave_especial do user nível 3 e do user nível 2 que puxei através da tabela user_detail

Ao gerar o log de movimentação, a chave_especial do user nivel 3 será salva na coluna chave_especial_user e a chave do nivel 2, será salva em chave_especial_subuser da tabela saldo_log

Através desta organização de tabelas pensei:

Assim que logado o usuário, vou buscar através da chave_especial_user registros nas colunas chave_especial_user e, de acordo com o nível de login, chave_especial_subuser na tabela saldo_log.

Se tiver registros da chave especial do usuário logado na coluna chave_especial_subuser, eu realizo uma subquery ou uma nova consulta para pegar o nome desse meu subusuario na tabela user_login baseado na sua chave especial que obtive em saldo_log > chave_especial_subuser

Baseado nisso lhes pergunto:

Tal logica de busca está certa? é viável? é o "ideal" para não gerar redundância de dados e acionamentos ao bd?

Como poderia montar tal pesquisa numa unica query sem ter que ficar realizando querys distintas?

[Williams Duarte 431]

Como poderia montar tal pesquisa numa unica query sem ter que ficar realizando querys distintas?

Minha pergunta, oque você fez até agora, oque pesquisou sobre as subsquerys, join?

Postei na madrugada e você já quer um exemplo pronto?

Outra coisa, ou usa tudo em inglês, NOME da TABELA e CAMPOS ou somente português!

chave_especial_user

chave_especial_subuser

Coloque nome das chaves onde um programador bater o olho, saberá que se trata de uma Foreign key

user_id

subuser_id

UserId

SubUserId

Baseado nisso lhes pergunto:

Tal logica de busca está certa? é viável? é o "ideal" para não gerar redundância de dados e acionamentos ao bd?

Join e Subquerys efetua uma única consulta, se construir a query e otimizar o DB da forma correta não haverá redundância. Para isso crie os índices dentro dos padrões corretamente, caso tenha dificuldade em criar os índices, use programas como Toad Data Maker ou Workbench se for MySQL

[Guilherme Luiz 41]

Olá Williams,

Não quero um exemplo pronto. O meu questionamento, e que pode ser de tantos outros, é se tal logica de consulta está correta!

Talvez, não só por questão de código, por questão de lógica de consulta e relacionamento de dados possa ter alguém que já tenha passado por algo que estou passando e por isso saiba me orientar melhor em relação a lógica de busca de dados.

Se você reparar, nao quero um código, quero apenas criticas/sugestões construtivas em relação ao relacionamento dos dados nas tabelas e logicas de busca/fluxo que possa me ajudar assim como você fez sobre os nomes das tabelas/colunas.

Porque até o momento, tenho código e sei como faze-lo, porem, tenho duvidas, mais uma vez, na logica e relacionamento de dados. Dúvidas se isso está de acordo com a melhor forma de se realizar uma busca nesse sentido utilizando uma unica query com a soma de subquerys ou join pra não gerar processamento de dados desnecessários.

[Williams Duarte 431]

Vamos lá, pra você entender como Join e Subquery trata os dados de pesquisa, você teria que rever sobre teoria dos conjuntos.

Selecionei um video bacana explicando e que cai bem sobre sua dúvida.

Dúvidas se isso está de acordo com a melhor forma de se realizar uma busca nesse sentido utilizando uma unica query com a soma de subquerys ou join pra não gerar processamento de dados desnecessários.

Subquerys são filtros, vai se acostumando, você vai usar no dia a dia em sistema mais complexos, não se preocupe os SGDBs sabem tratar muito bem delas, desde que saiba oque esta fazendo.

Bom, se você quer saber se vai ter problemas com performance e quer opinião de alguém que entenda a respeito, comece modelando sua ER de forma correta e postando no fórum certo, que é de modelagem de dados.

Porque não adiantara nada falarmos de algo complexo, se o básico for ignorado.

[Guilherme Luiz 41]

Bacana Will!
vou assistir o video e assim que conseguir modelar a consulta eu posto aqui e ai vamos comentando pontualmente!

Obrigado pelos toques!

Feriadao vou torrar mente com isso! :)

[Williams Duarte 431]

Igualmente!


Segue o de modelagem de dados, gerando o diagrama fica fácil para te ajudarem, e poste no fórum correto, aqui é PHP. ;)

[Guilherme Luiz 41]

Apenas para encerrar o tópico, fiz a seguinte query:

"SELECT consumo_log.servico AS servico, 
consumo_log.valor AS valor, 
consumo_log.data AS data, 
user_login.user AS usuario 
FROM consumo_log INNER JOIN user_login ON consumo_log.chave_especial_user = user_login.chave_especial
WHERE consumo_log.chave_especial_subuser='$chave_especial'
ORDER BY consumo_log.data DESC
 LIMIT $reg_pagina";

Nesta query eu busco a minha relação com um subusuario para imprimir o extrato de consumo dele.

"Me encontrando" na coluna chave_especial_subuser na tabela user_detail, eu utilizo a chave_especial deste meu subusuario para encontrar o nome do login dele na tabela user_login para poder imprimir na tela.

Não foi dificil fazer, o meu "problema" era a logica da consulta que acabei mudando no final de tudo.