Join twice against a CTE

[google...@sql-workbench.net]

We have a query that joins a single CTE twice, something like this (simplified):

with cte (id, type, data) as (
  select st.id, st.type, st.data
  from some_table st
  where st.type in (1,2)
)
select ...
from other_table  
  join cte as t1 on t1.id = x.id and t1.type = 1
  join cte as t2 on t2.id = x.id and t2.type = 2;

Now I have a hard time translating this to QueryDSL:

QSomeTable st = new QSomeTable("st");
QOtherTable ot = new QOtherTable("ot");

PathBuilder<Tuple> cte = new PathBuilder<>(Tuple.class, "cte");
PathBuilder<Tuple> cte2 = ?????

SQLQuery sqlQuery = factory.query()
.with(cte, st.id, st.type, st.data).as(
    select(st.id, st.type, st.data).
    from(st).
    where(...)
).
select(...).
from(ot).
   join(cte).on(ot.id.eq(cte.get(cte.id))).
   join(cte2) ????

How can I define an alias on an existing PathBuilder? cte.as("cte2") in the join condition doesn't work.

Any ideas?

[Richard Richter]

Hi,

I don't work with SQL query, but I guess it's the same like with JPA. You just name the second alias differently: new PathBuilder<>(Tuple.class, "cte2").

And in select part you do the same you do in your raw select:
.join(cte2).on(ot.id.eq(cte2.id)) (do you need that get there?)

Also I noticed, that original query has 2 conditions on each ON - so you actually probably want .on(ot.id.eq(cte2.id).and(cte2.type.eq(2))).

It really should say the same the raw select says.

Virgo

[google...@sql-workbench.net]

Am Dienstag, 13. September 2016 12:53:40 UTC+2 schrieb Richard Richter:

Hi,

I don't work with SQL query, but I guess it's the same like with JPA. You just name the second alias differently: new PathBuilder<>(Tuple.class, "cte2").
And in select part you do the same you do in your raw select:
.join(cte2).on(ot.id.eq(cte2.id)) (do you need that get there?)

 

That is not the same thing.

QueryDSL will generate something like:

with cte (id, type, data) as (

...
)
select ...
from other_table  
  join cte on cte.id = x.id and cte.type = 1
  join cte2 on cte2.id = x.id and cte2.type = 2;

Note that join cte2 is wrong, there is no cte2 in the query. It should be join cte as cte2

Also I noticed, that original query has 2 conditions on each ON - so you actually probably want .on(ot.id.eq(cte2.id).and(cte2.type.eq(2))).

Yes, I forgot to add that in the sample Java code.