Apanhando com JOIN

Assuntos Gerais
#1

Galera, tudo bem?

Estou com dificuldade em fazer join no Hibernate.

Escrevi o seguinte HQL:

select e  from br.com.kbpm.entity.gr.Grper010 e
  join fetch e.detailMap 
 where  e.detailMap.enddt  >=  :detailMapenddtInitial 
   and  e.detailMap.enddt  <=  :detailMapenddtFinal 
   and  e.percd  =  :percd

“enddt” é um campo DATE, ou seja, quero selecionar um range entre duas datas, no objeto detalhe. “percd” é uma UK do objeto principal

Me retornar um SQL assim, que gera produto cartesiano:

select grper010x0_.tabid as tabid0_0_
     , detailmap1_.tabid as tabid1_1_
     , grper010x0_.recvr as recvr0_0_
     , grper010x0_.frgnr as frgnr0_0_
     , grper010x0_.ident as ident0_0_
     , grper010x0_.percd as percd0_0_
     , grper010x0_.pertp as pertp0_0_
     , detailmap1_.recvr as recvr1_1_
     , detailmap1_.enddt as enddt1_1_
     , detailmap1_.mstid as mstid1_1_
     , detailmap1_.doc01 as doc4_1_1_
     , detailmap1_.doc02 as doc5_1_1_
     , detailmap1_.doc03 as doc6_1_1_
     , detailmap1_.fname as fname1_1_
     , detailmap1_.rname as rname1_1_
     , detailmap1_.mstid as mstid0__
     , detailmap1_.tabid as tabid0__
     , detailmap1_.enddt as formula0_0__ 
  from Grper010 grper010x0_ 
 inner join Grper010p detailmap1_ 
    on grper010x0_.tabid=detailmap1_.mstid
     , Grper010p detailmap2_
     , Grper010p detailmap3_ 
 where grper010x0_.tabid=detailmap3_.mstid 
   and grper010x0_.tabid=detailmap2_.mstid 
   and detailmap2_.enddt>=? 
   and detailmap3_.enddt<=? 
   and grper010x0_.percd=?

Entendo que deveria retornar algo assim:

select grper010x0_.tabid as tabid0_0_
     , detailmap1_.tabid as tabid1_1_
     , grper010x0_.recvr as recvr0_0_
     , grper010x0_.frgnr as frgnr0_0_
     , grper010x0_.ident as ident0_0_
     , grper010x0_.percd as percd0_0_
     , grper010x0_.pertp as pertp0_0_
     , detailmap1_.recvr as recvr1_1_
     , detailmap1_.enddt as enddt1_1_
     , detailmap1_.mstid as mstid1_1_
     , detailmap1_.doc01 as doc4_1_1_
     , detailmap1_.doc02 as doc5_1_1_
     , detailmap1_.doc03 as doc6_1_1_
     , detailmap1_.fname as fname1_1_
     , detailmap1_.rname as rname1_1_
     , detailmap1_.mstid as mstid0__
     , detailmap1_.tabid as tabid0__
     , detailmap1_.enddt as formula0_0__ 
  from Grper010 grper010x0_ 
 inner join Grper010p detailmap1_ 
    on grper010x0_.tabid=detailmap1_.mstid
 where detailmap1_.enddt>=? 
   and detailmap1_.enddt<=? 
   and grper010x0_.percd=?

Por que ele acrescenta 2 copias extras da mesma tabela?

Grato,
Carlos Buosi.

#2

[quote=buosinet]Galera, tudo bem?

Estou com dificuldade em fazer join no Hibernate.

Escrevi o seguinte HQL:

select e  from br.com.kbpm.entity.gr.Grper010 e
  join fetch e.detailMap 
 where  e.detailMap.enddt  >=  :detailMapenddtInitial 
   and  e.detailMap.enddt  <=  :detailMapenddtFinal 
   and  e.percd  =  :percd

“enddt” é um campo DATE, ou seja, quero selecionar um range entre duas datas, no objeto detalhe. “percd” é uma UK do objeto principal

Me retornar um SQL assim, que gera produto cartesiano:

select grper010x0_.tabid as tabid0_0_
     , detailmap1_.tabid as tabid1_1_
     , grper010x0_.recvr as recvr0_0_
     , grper010x0_.frgnr as frgnr0_0_
     , grper010x0_.ident as ident0_0_
     , grper010x0_.percd as percd0_0_
     , grper010x0_.pertp as pertp0_0_
     , detailmap1_.recvr as recvr1_1_
     , detailmap1_.enddt as enddt1_1_
     , detailmap1_.mstid as mstid1_1_
     , detailmap1_.doc01 as doc4_1_1_
     , detailmap1_.doc02 as doc5_1_1_
     , detailmap1_.doc03 as doc6_1_1_
     , detailmap1_.fname as fname1_1_
     , detailmap1_.rname as rname1_1_
     , detailmap1_.mstid as mstid0__
     , detailmap1_.tabid as tabid0__
     , detailmap1_.enddt as formula0_0__ 
  from Grper010 grper010x0_ 
 inner join Grper010p detailmap1_ 
    on grper010x0_.tabid=detailmap1_.mstid
     , Grper010p detailmap2_
     , Grper010p detailmap3_ 
 where grper010x0_.tabid=detailmap3_.mstid 
   and grper010x0_.tabid=detailmap2_.mstid 
   and detailmap2_.enddt>=? 
   and detailmap3_.enddt<=? 
   and grper010x0_.percd=?

Entendo que deveria retornar algo assim:

select grper010x0_.tabid as tabid0_0_
     , detailmap1_.tabid as tabid1_1_
     , grper010x0_.recvr as recvr0_0_
     , grper010x0_.frgnr as frgnr0_0_
     , grper010x0_.ident as ident0_0_
     , grper010x0_.percd as percd0_0_
     , grper010x0_.pertp as pertp0_0_
     , detailmap1_.recvr as recvr1_1_
     , detailmap1_.enddt as enddt1_1_
     , detailmap1_.mstid as mstid1_1_
     , detailmap1_.doc01 as doc4_1_1_
     , detailmap1_.doc02 as doc5_1_1_
     , detailmap1_.doc03 as doc6_1_1_
     , detailmap1_.fname as fname1_1_
     , detailmap1_.rname as rname1_1_
     , detailmap1_.mstid as mstid0__
     , detailmap1_.tabid as tabid0__
     , detailmap1_.enddt as formula0_0__ 
  from Grper010 grper010x0_ 
 inner join Grper010p detailmap1_ 
    on grper010x0_.tabid=detailmap1_.mstid
 where detailmap1_.enddt>=? 
   and detailmap1_.enddt<=? 
   and grper010x0_.percd=?

Por que ele acrescenta 2 copias extras da mesma tabela?

Grato,
Carlos Buosi.[/quote]

Por que você não usa between para fazer a range de datas ?

#3

Não estas a usar os campos do join.

Experimenta assim:

select e from br.com.kbpm.entity.gr.Grper010 e join fetch e.detailMap m where m.enddt >= :detailMapenddtInitial and m.enddt <= :detailMapenddtFinal and e.percd = :percd

Ou, usando o between:

select e from br.com.kbpm.entity.gr.Grper010 e join fetch e.detailMap m where m.enddt between :detailMapenddtInitial and :detailMapenddtFinal and e.percd = :percd

#4