Doubt with Joins in HQL query

1

I have the following problem when performing a query in the HQL database

The tabela1 table has approximately 20,000 records.

The other tables have "N" records.

The tabela1 table has relationships with the other tables tabela2 , tabela3 and tabela4 .

When executing this command below, I expected the return value to be equal to the number of records of tabela 1 , but I'm doing something wrong with JOINS because the command me returns a number greater than 200000;

select distinct count(t.id)
from tabela1 t  
left join t.tabela2 t2
left join t.tabela3 t3
left join t.tabela4 t4 
where t2.campoInteiro >-1 ;

The tables look like this:

@OneToMany(cascade = CascadeType.ALL)
 List<Tabela2> tabela2;
 @OneToMany(cascade = CascadeType.ALL)
 List<Tabela3> tabela3;
 @OneToMany(cascade = CascadeType.ALL)
 List<Tabela4> tabela4;
    
asked by anonymous 23.02.2018 / 17:45

1 answer

0

You need to define how the tables connect. As it stands, all lines of t1 join those of t2 , t3 and t4 .

select distinct count(t.id)
from tabela1 t  
left join t.tabela2 t2 on t2.fk_t1 = t1.pk_t1
left join t.tabela3 t3 on t3.fk_t2 = t2.pk_t2
left join t.tabela4 t4 on t4.fk_t3 = t3.pk_t3
where t2.campoInteiro > -1 ;
    
23.02.2018 / 17:50