self-join with where clause

[57skies]

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?

Thx.

[Richard Richter]

Hi,

this is logical, but not JPA way how to do it. I personally prefer it this "more SQL like" way, but you need at least Hibernate 5.1 - what is your version?

Pure JPA way would be to use "association path" in a join, what you used is "root entity" which is not allowed by JPA 2.1 spec. This is also sometimes referred as "ad-hoc join" and actually all JPA 2.1 providers support it (EclipseLink for couple of years, Hibernate since 5.1).

You may try to avoid it using:

query.from(serviceProvider2)

    .innerJoin(serviceProvider2.enterprise, serviceProvider3) // so service3 will be alias for the path serviceProvider2.enterprise

//    .on(serviceProvider2.enterprise.id.eq(serviceProvider3.id)) // not necessary, JPA 2.1 will imply it

    .where(serviceProvider3.id.eq(enterpriseId))

    .list(serviceProvider2.id);

Hope, I got it right. :-)

Cheers

Virgo

[57skies]

Hi Richard,

Yes sir, u got it right indeed. :-) I have to admit that this is at least weird looking. 

And yes I am in hibernate 5.1.0. Is there another way to have it done? More SQL-ish?

Thx,

Eugene. 

[Richard Richter]

Your first attempt actually wasn't that bad in being more SQL-ish, the trouble is that "serviceProvider2.enterprise.id" is - when reasonable beings like you and me look at it - obviously the same like "serviceProvider2.enterprise_id" in SQL. But not so for ORM. Smart ORMs sometimes resolve it this way, but there is simply no guarantee - for them it's "join the enterprise association (which is the additional cross join) and find the id column there".

But we can do a little neat trick - map both the relation AND it's raw value (I call this "dual mapping"):

@ManyToOne

@JoinColumn(name = "enterprise_id")

private ServiceProvider enterprise;

@Column(name = "enterprise_id", updatable = false, insertable = false)

private Integer enterpriseId; // or Long or whatever is needed

Now notice that one of these MUST be updatable/insertable false - it will be filled in when read, but only one of those attributes is relevant for writing, otherwise it would be mess. Now when we have the raw value mapped, we can try something like:

query.from(serviceProvider2)

    .innerJoin(serviceProvider3).on(serviceProvider2.enterpriseId.eq(serviceProvider3.id))

    .where(serviceProvider3.id.eq(enterpriseId))

    .list(serviceProvider2.id);

This is virtually 1-to-1 with the generated underlying SQL.

BTW: with current where it doesn't make sense to join in the first place (unless I'm missing something) - if it was where about something else on serviceProvider3, it would make sense, but because it's PK of serviceProvider3, why not try this?

// with dual mapping

query.from(serviceProvider2)

    .where(serviceProvider2.enterpriseId.eq(enterpriseId))

    .list(serviceProvider2.id);

// with original mapping

query.from(serviceProvider2)

    .where(serviceProvider2.enterprise.id.eq(enterpriseId))

    .list(serviceProvider2.id);

The second one may result in unnecessary join, but I think Hibernate optimizes this case (I'm lazy to try now, sorry ;-)). JPA in general does not guarantee it will not throw some silly join in.

Cheers

Virgo

[57skies .]

Hey Richard,

Yes... To everything I guess :-) you are right about all your examples. I wanted to put the enterprise_id in the entity too, but did not like it having there too much. I guess updatable and insertable false with proper documentation makes it very clear. I like this advice a lot btw ;).

Having the dual mapping makes things so much nicer, while without it hibernate will indeed create the cross join and the results will not even be correct.

Again, thx for your time.

Cheers,
Eugene.

--
You received this message because you are subscribed to a topic in the Google Groups "Querydsl" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/querydsl/DmM5MbO0RKA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to querydsl+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.