There are several problems that can come up when size() is used as a select expression.
Using the following code:
{code:java}
@Entity(name ="Company")
public class Company {
@Id
private int id;
private String name;
@ManyToMany(cascade = CascadeType.ALL,fetch = FetchType.EAGER)
private List<Customer> customers = new ArrayList<>();
}
@Entity(name = "Customer")
public static class Customer {
@Id
private int id;
private String name;
}
public static class CompanyDto {
public int id;
public String name;
public int sizeCustomer;
public CompanyDto(int id, String name, int sizeCustomer){
this.id = id;
this.name = name;
this.sizeCustomer = sizeCustomer;
}
}
{code}
1) Results from the following query do not include an {{Company}} with an empty {{customers}} collection:
JPA spec, 4.6.17.2.2 Arithmetic Functions, says, "The SIZE function returns an integer value, the number of elements of the collection. If the collection is
empty, the SIZE function evaluates to zero."
{code:java}
select new org.hibernate.test.hql.size.ManyToManySizeTest$CompanyDto(c.id, c.name, size( c.customers ) )
from Company c
group by c.id, c.name
order by c.id
{code:java}
because the generated SQL is:
{code:sql}
select
manytomany0_.id as col_0_0_,
manytomany0_.name as col_1_0_,
count(customers1_.Company_id) as col_2_0_
from
Company manytomany0_ cross
join
Company_Customer customers1_
where
manytomany0_.id=customers1_.Company_id
group by
manytomany0_.id ,
manytomany0_.name
order by
manytomany0_.id
{code:sql}
A left outer join should have been used to join Company and Company_Customer tables.
----
Original description:
I'm using the following HQL and it return a correct result :
{code:java}
select distinct new net.rhuanrocha.dto.CompanyDto(c.id,c.name,0)
from Company c left join c.customers cu
where c.name =:name
group by c.id, c.name
{code}
But when I use the size function in my HQL the query results is wrong (In my case is returning an empty result). Look this HQL:
{code:java}
select distinct new net.rhuanrocha.dto.CompanyDto(c.id,c.name,size(c.customers))
from Company c left join c.customers cu
where c.name =:name
group by c.id, c.name
{code}
|
|
