QueryDSL join structure

[David Magalhães]

Hi,

I'm creating a request to the database using QueryDSL. After some time, I finally got the correct QueryDSL to request, but I was thinking why the following query is the opposite of the SQL version of it, and I can't find an answer.

Example, a SQL query:

SELECT n.id, network_name, count(DISTINCT a.id), count(DISTINCT w.id), count(DISTINCT v.id) 
FROM network n
  LEFT JOIN account a ON a.network_id = n.id
  LEFT JOIN meat w ON w.account_id = a.id
  LEFT JOIN fish v ON v.account_id = a.id
GROUP BY n.id, network_name

To get the same results on QueryDSL, I need to specify the query in the following format:

new JPAQuery(entityManager).from(meat, fish)
        .rightJoin(meat.account, account)
        .rightJoin(fish.account, account)
        .rightJoin(account.network, network)
        .groupBy(network)
        .where(network.id.eq(id))
        .singleResult(network, account.id.countDistinct(), fish.id.countDistinct(), meat.id.countDistinct());

My question is, why I need to select the tables meat and fish first on QueryDSL instead of normal network in SQL ? And because of that invert all the logic, using rightJoin instead of leftJoin and invert the order I used the joins also. I think is a little bit confusing when we are thinking in the SQL query to do it.

[Richard Richter]

Hi David

Try to do the same with JPQL and you will see why you have to do it this way with Querydsl. It's not Querydsl's problem, it merely states JPQL in a nicer fashion. I can only guess your model, but the most critical thing is, that in SQL you can JOIN ON arbitrary condition. In JPA you can only join across relations. If you had Network.accounts field (probably @OneToMany(mappedBy = "network"), just guessing) than you could write ".from(network).leftJoin(network.accounts, account)".

In my eyes JOINing only across relations is the most limiting thing on JPA. Actually, EclipseLink allows you to do ".from(network).leftJoin(account).on(network.id.eq(account.network.id))" for instance, but this is not JPA compliant. The problem is not with ON actually, but in the fact, that JPA does NOT allow JOIN clause with "root entity" (e.g. account here), only with association paths (e.g. network.accounts higher). We use this non-JPA thing with EclipseLink and Querydsl doesn't complain at all. BTW: OneToMany with LAZY fetch is not a big deal if you can have the dependency to Account in Network entity, so it may solve your problem.

Virgo

[David Magalhães]

Thanks for the explanation. The structure here is Network > Account > (Meat or Fish). I have already with ExtraLazy the list of accounts on the network, but I still couldn't use "the normal logic".