Convert SQL query that contains WITH clause and union in QueryDsl

I'm using Oracle DB and has Students table:

Students Table:

| COLUMN      | TYPE          |
| ----------- | ------------- |
|  ID         | VARCHAR2(200) |
|  NAME       | VARCHAR2(200) |
|  AGE        | NUMBER(5)     |
|  MAIN_CITY  | VARCHAR2(200) |
|  GENDER     | VARCHAR2(50)  |
|  SCORE      | FLOAT(5)      |

`

I'm trying to convert this SQL query to query-dsl code:

SELECT /*+ parallel(2)*/ *
FROM (WITH filtered_values(age, gender, main_city) AS
(
SELECT /*+ MATERIALIZE */ age, gender, main_city 
FROM Students
-- GET CONDITIONS FROM USER AND FILTER THE QUERY USING WHERE
)
SELECT * 
FROM (
SELECT age AS value, COUNT(*) AS row_count, 'age' AS column_name
FROM filtered_values
GROUP BY age
ORDER BY row_count)
WHERE rownum <= 5
UNION ALL (
SELECT * 
FROM (
SELECT gender AS value, COUNT(*) AS row_count, 'gender' AS column_name
FROM filtered_values
GROUP BY gender
ORDER BY row_count)
WHERE rownum <= 5)
UNION ALL (
SELECT * 
FROM (
SELECT main_city AS value, COUNT(*) AS row_count, 'main_city' AS column_name
FROM filtered_values
GROUP BY main_city
ORDER BY row_count)
WHERE rownum <= 5)
)

The filters that available from client is flexible and change the WITH query, for example:

where score greater than 20:
> WITH filtered_values(age, gender, main_city) AS
> (
> SELECT /*+ MATERIALIZE */ age, gender, main_city
> FROM Students
> WHERE score > 20
> )

where city is LA or NYC, and score is between 50-60:
> WITH filtered_values(age, gender, main_city) AS
> (
> SELECT /*+ MATERIALIZE */ age, gender, main_city
> FROM Students
> WHERE main_city IN ('LA', 'NYC') AND score >= 50 AND score <= 60
> )

I'm using QueryDsl version 5.0.0.

I've created Java classes:

Student:

@Data
@Entity
@NoArgsConstructor
@AllArgsConstructor
@Table(schema = "PROD", name = "Students")
public class Student implements Serializable {
@Id
@Column(name = "ID")
private String id;
@Column(name = "NAME")
private String name;
@Column(name = "AGE")
private int age;
@Column(name = "MAIN_CITY")
private String mainCity;
@Column(name = "SCORE")
private float score; 
}

AggregationValues:

@Data
@Entity
@NoArgsConstructor
@AllArgsConstructor
@Table(schema = "PROD", name = "NOT_REAL_TABLE")
public class AggregationValues {
private int age;
private String gender;
private String mainCity;
}

AggregationResultRow:

@Data
@NoArgsConstructor
@AllArgsConstructor
public class AggregationResultRow {
private String value;
private long rowCount;
private String columnName;
}

My code is:
> Note that this.getConditions(aggregationsRequest) return BooleanBuilder with all the filters from the client.

private final QStudent table = QStudent.student;
private final QAggregationValues aggregationValues = QAggregationValues.aggregationValues;
public List<AggregationResultRow> getAggergations(AggregationRequest aggregationRequest) {
SubQueryExpression<AggregationValues> filteredValues = new BlazeJPAQuery<AggregationValues>(entityManager, criteriaBuilderFactory)
.select(Projections.constructor(AggregationValues.class, 
this.table.age,
this.table.gender,
this.table.mainCity))
.from(this.table)
.where(this.getConditions(aggregationRequest));
BlazeJPAQuery<AggregationResultRow> ageSelect = new BlazeJPAQuery<AggregationResultRow>(entityManager, criteriaBuilderFactory)
.with(this.aggregationValues, filteredValues)
.select(Projections.constructor(AggregationResultRow.class, this.aggregationValues.age.as("value"),
this.aggregationValues.count().as("row_count"),
Expressions.asString("age").as("column_name")))
.from(this.aggregationValues)
.groupBy(this.aggregationValues.age);
BlazeJPAQuery<AggregationResultRow> genderSelect = new BlazeJPAQuery<AggregationResultRow>(entityManager, criteriaBuilderFactory)
.with(this.aggregationValues, filteredValues)
.select(Projections.constructor(AggregationResultRow.class, this.aggregationValues.gender.as("value"),
this.aggregationValues.count().as("row_count"),
Expressions.asString("gender").as("column_name")))
.from(this.aggregationValues)
.groupBy(this.aggregationValues.gender);
BlazeJPAQuery<AggregationResultRow> mainCitySelect = new BlazeJPAQuery<AggregationResultRow>(entityManager, criteriaBuilderFactory)
.with(this.aggregationValues, filteredValues)
.select(Projections.constructor(AggregationResultRow.class, this.aggregationValues.mainCity.as("value"),
this.aggregationValues.count().as("row_count"),
Expressions.asString("main_city").as("column_name")))
.from(this.aggregationValues)
.groupBy(this.aggregationValues.mainCity);
return ageSelect.unionAll(genderSelect, mainCitySelect).fetch();
}

But I got Exception - java.lang.IllegalArgumentException: Don't mix union and from

I've tried more code ideas but nothing works and this one looks the closest to my query.

Any ideas?

Thank you very much, I'll appreciate it a lot!

So I found a half way solution!
What I’ve done is to add autowired parameter in class:

@Autowired
private JdbcTemplate jdbcTemplate;

And this is the function:

public List<AggregationResultRow> getAggregations(AggregationRequest aggregationRequest) throw Exception {
Connection connection = this.jdbcTemplate.getDataSource().getConnection();
OracleQuery<AggregationResultRow> query = new OracleQuery<AggregationResultRow>(connection)
.with(this.aggregationValues, new OracleQuery<AggregationValues>(connection)
.select(Projections.constructor(AggregationValues.class, this.table.age.as(this.aggregationValues.age), this.table.gender.as(this.aggregationValues.gender),
this.table.mainCity.as(this.aggregationValues.mainCity)))
.from(this.table)
where(this.getConditions(aggregationRequest)));
query.unionAll(new OracleQuery<AggregationResultRow>(connection)
.select(Projections.constructor(AggregationResultRow.class, this.aggregationValues.age.as(“value”),
this.aggregationValues.age.count().as(“row_count”),
Expressions.asString(“age”).as(“column_name”)))
.from(this.aggregationValues)
.groupBy(this.aggregationValues.age),
new OracleQuery<AggregationResultRow>(connection)
.select(Projections.constructor(AggregationResultRow.class, this.aggregationValues.gender.as(“value”),
this.aggregationValues.gender.count().as(“row_count”),
Expressions.asString(“gender”).as(“column_name”)))
.from(this.aggregationValues)
.groupBy(this.aggregationValues.gender),
new OracleQuery<AggregationResultRow>(connection)
.select(Projections.constructor(AggregationResultRow.class, this.aggregationValues.mainCity.as(“value”),
this.aggregationValues.mainCity.count().as(“row_count”),
Expressions.asString(“mainCity”).as(“column_name”)))
.from(this.aggregationValues)
.groupBy(this.aggregationValues.mainCity));
return query.fetch();
}

Now I only need to add Materialize to the select in the WITH clause and parallel to all the function. Any ideas?