inner join querydsl with where clause

Question

Suppose I have this table service_providers that has a self reference via an enterprise_id column.

 id            enterprise_id

 102         57
 103         57



  public class ServiceProvider {
       ....
       @ManyToOne
       @JoinColumn(name = "enterprise_id")
       private ServiceProvider enterprise;
 }

What I am trying to do si having an enterprise_id get all servive_providers. This is pretty easy in SQL:

  select sp1.id
  from service_providers sp1 
  inner join service_providers sp2 on (sp1.enterprise_id=sp2.id) 
  where sp2.id=57;

But when trying to replicate that via query-dsl I somehow get into problems.

Here is what is looks like :

  QServiceProvider serviceProvider2 = new   QServiceProvider("serviceProvider2");
  QServiceProvider serviceProvider3 = new QServiceProvider("serviceProvider3");



    query.from(serviceProvider2)
            .innerJoin(serviceProvider3)
            .on(serviceProvider2.enterprise.id.eq(serviceProvider3.id))
            .where(serviceProvider3.id.eq(enterpriseId))
            .list(serviceProvider2.id);

This is what hibernate generates :

 select  sp1.id 
 from service_providers sp0 
 cross join service_providers sp1 
 inner join service_providers sp2 on (sp1.enterprise_id=sp2.id) 
 where sp0.id=? and sp2.id=?

This is a little bit confusing, besides being wrong.

Can anyone tell me what I am doing wrong here?

Answer 1

Basically, with the help from the guys at the querydsl forums, here are the important bits here: Pure JPA way would be to use "association path" in a join, what I used is "root entity" which is not allowed by JPA 2.1 spec

The correct query would then look like this :

 query.from(serviceProvider2)
            .innerJoin(serviceProvider2.enterprise, serviceProvider3)
            .where(serviceProvider3.id.eq(enterpriseId))
            .list(serviceProvider2.id);