querydsl-sql: Howto query a union of sub queries using aliases

[nyc2]

Hi,

I got stuck while trying to create a (relatively) complex query like this one:
select sub.col,sub.alias,count(*) from (
 select distinct t.col1,(case when t.col2='...' then 1 else 0 end) alias, ... from QTable t join ... where ...
 union
 select distinct t.col1,(case when t.col2='...' then 1 else 0 end) alias, ... from QTable t join ... where ...
 union ...
) sub
group by sub.alias, sub. ...

What I currently have is:
SQLSubQuery sub = new SQLSubQuery();
for(String str : strs) {
 SQLSubQuery subUnion = new SQLSubQuery().from(Table).join(...).on(...).where(...);
 PathBuilder<Object[]> subAlias = new PathBuilder<Object[]>(Object[].class, "sub");
 Expression<Boolean> alias = new CaseBuilder().when(Table.col2.eq(...)).then(true).otherwise(false);
 sub.union(subUnion.listDistinct(Table.col1, alias, ...)); // not available
}
query().from(sub, Path<?> alias).groupBy(sub.alias, sub. ...).list(sub.col, sub.alias, count(*)); // how to do this?

Could you please help me with this query? Thank you so much.

[nyc2]

Ok, I've found out now that SQLSubQuery supports distinct().list(...) instead of listDistinct(...) available in ProjectableQuery. This is a bit odd, is there a reason?

However, I still have problems with the subquery alias and referencing columns of this alias. I always get as sub.* no matter what I'm trying to do.

Any help?

[Timo Westkämper]

Hi.

Sorry for the late reply. I managed to create something like this with Querydsl test types :

  QSurvey survey = QSurvey.survey;
       
  // create sub queries
  List<SubQueryExpression<Object[]>> sq = new ArrayList<SubQueryExpression<Object[]>>();
  String[] strs = new String[]{"a","b","c"};
  for(String str : strs) {
      Expression<Boolean> alias = Expressions.cases().when(survey.name.eq(str)).then(true).otherwise(false);
      sq.add(sq().from(survey).distinct().unique(survey.name, alias));            
  }
       
  // master query

  PathBuilder<Object[]> subAlias = new PathBuilder<Object[]>(Object[].class, "sub");       

  List<Object[]> results = query()
      .from(sq().union(sq).as(subAlias))
      .groupBy(subAlias.get("prop1"))
      .list(subAlias.get("prop2"));

I made some changes to Querydsl code to simplify the construction of such queries.

The distinct flag usage will probably in the near future be changed to work on top level queries in the same way like for sub queries.

Br,
Timo

--
Timo Westkämper
Mysema Oy
+358 (0)40 591 2172
www.mysema.com

[nyc2]

Hi Timo,

thanks for your permanent good support! Keep it up!

Unfortunately, as(subAlias) is not available on SQLSubQuery.union(SubQueryExpression... sq). I am using 2.2.4-BUILD-SNAPSHOT.


Best regards,
nyc2

[Timo Westkämper]

Hi.

I just uploaded a new snapshot.

Timo

[nyc2]

Hi,

thanks for uploading the snapshot. However, as(subAlias) as shown in your example code gets rendered to sub.* which leads to a SQLException:
12:27:21,773 WARN  [org.hibernate.util.JDBCExceptionReporter] SQL Warning: 102, SQLState: 42000
12:27:21,775 WARN  [org.hibernate.util.JDBCExceptionReporter] Preparing the statement failed: Incorrect syntax near '.'.
12:27:21,782 WARN  [org.hibernate.util.JDBCExceptionReporter] SQL Error: 102, SQLState: 42000
12:27:21,784 ERROR [org.hibernate.util.JDBCExceptionReporter] Incorrect syntax near '.'.
12:27:21,785 WARN  [org.hibernate.util.JDBCExceptionReporter] SQL Error: 156, SQLState: S1000
12:27:21,786 ERROR [org.hibernate.util.JDBCExceptionReporter] Incorrect syntax near the keyword 'as'.

What's wrong with it?


Best regards,
nyc2

[Timo Westkämper]

Ok, this appears to be a bug in Querydsl JPA Native query support. I was always testing on the Querydsl SQL side, that's why I didn't encounter it before.