JPQL count em tabelas relacionadas usando operador OR

Infraestrutura Banco de Dados
, , ,
#1

Olá Pessoal, estou com dificuldade em criar uma consulta em JPQL. Em SQL eu consigo. Estou usando PostgreSql. Meu caso é o seguinte: tenho uma entidade Principal que se relaciona com uma entidade chamada Especies.

Há uma relacionamento 1:N (Principal especieID x Especies id).
As colunas importantes destas tabelas são: Principal (id (Long); especieid (Especies)). Especies (id (Long), especie (String)).

Eu quero contar quantas espécies estão sem identificação, só que no banco existem duas formas de espécies não identificadas: 1ª quando na tabela Principal a coluna especieID é igual a nulo; 2ª quando na tabela principal está relacionada com a espécie que foi chamada de “Não Informado”;

Em SQL minha consulta ficou assim:

Select COUNT(Distinct(principal.id)) 
From Principal, Especie
Where principal.especieid IsNull OR (principal.especieid = especie.id 
AND Lower(especie.especie) = 'não informado')

Resultado = 17 (são 15principal.especieid = Null e 2 principal.especieid.especie = "não informado")

Em JPQL tentei fazer assim:

SELECT Count(p.id) From Principal p 
Where p.especieid Is Null  OR p.especieid.especie = 'Não Informado'

Resultado = 2 (principal.especieid.especie = "não informado")

Contudo se eu faço duas JPQLs diferentes, separando o as sentenças antes e depois do OR ele traz corretamente, só que em duas consultas separadas:

SELECT Count(p.id) From Principal p 
Where p.especieid Is Null

Resultado = 15

SELECT Count(p.id) From Principal p 
Where p.especieid.especie = 'Não Informado'

Resultado = 2

Como eu poderia escrever a consulta SQL em JPQL?

#2

Você já tem a solução para atender a funcionalidade. Escrever SQL diretamente é muito mais prático e sem esses mistérios do JPQL.

Sobre o problema que você perguntou, monitore e poste aqui o SQL que o JPQL está gerando, assim facilita começar a entender qualquer problema do tipo.

#3

Desculpe a ignorância, mas como eu faço para pegar o SQL gerado pelo JPQL?

#4

Dá uma olhada aqui: [Resolvido] Visualizar a query SQL executada no Hibernate Ou pesquise sobre show_sql

#5

O SQL gerado foi o seguinte:

SELECT COUNT(t0.id) 
FROM principal t0, especie t1 
WHERE (((t0.especieid IS NULL) OR (LOWER(t1.especie) = ?))
             AND (t1.id = t0.especieid))

bind => [não informado]

Estranho, para mim, o SQL gerado tem a mesma sintaxe do sql que coloquei na pergunta inicial.
Alguém consegue decifrar?

#6

Está diferente. Não vi a query toda, mas logo na primeira linha já deu pra ver que você fez distinct escrevendo diretamente SQL, já no jpql você não está usando recurso dele pra gerar o distinct. Não sei se o problema é só esse, o objetivo da sua query é bem confuso, mas enfim, é uma diferença identificada. Outra coisa que pode ver é se as associações/relacionamentos estão mapeadas corretamente.

#7

Além do distinct, o where também está diferente na parte do “OR” onde você precisa isolar nos parenteses. Fica uma zona esse SQL gerado pelo jpql, dificultando a leitura com esse excesso de parenteses, então retirei os parenteses desnecessários só pra facilitar a leitura do SQL que o jqpl gerou:

WHERE (t0.especieid IS NULL OR LOWER(t1.especie) = ?)
AND t1.id = t0.especieid

Repare que está diferente do SQL que você escreveu do jeito que quer:

Where principal.especieid IsNull OR (principal.especieid = especie.id 
AND Lower(especie.especie) = 'não informado')

Lembre-se do uso correto do parenteses no OR de acordo com as condições desejadas.

#8

javaflex, muito obrigado por sua atenção!
Então o problema era realmente na questão dos parenteses. O SQL gerado estava colocando os parenteses em locais diferentes do meu SQL original.
Para conseguir resolver olhei este link aqui

No caso, usei a função IN e fiz um Select antes e outro depois do OR, assim:

SELECT Count(Distinct(p.id)) From Principal p
Where p.id IN (Select p1.id From Principal p1 Where p1.especieid Is Null) 
OR
p.id IN (Select p2.id From Principal p2 Where (Lower(p2.especieid.especie) = 'não informado'))

O SQL gerado foi o seguinte:

SELECT COUNT(DISTINCT(t0.id)) FROM principal t0 
WHERE (t0.id IN (SELECT t1.id FROM principal t1 WHERE (t1.especieid IS NULL)) 
OR
t0.id IN (SELECT t2.id FROM especie t3, principal t2 WHERE ((LOWER(t3.especie) = 'não informado')
AND (t3.id = t2.especieid))))

Mais uma vez, muito obrigado pela atenção.