Querydsl--using postgresql's values

Question

Can I translate this sql into querydsl form?

select count(ppe),v.name 
from personal_progress_entity ppe left join user_detail_entity ude 
on ppe.student_entity_id=ude.user_id 
right join (values ('aaa'),('bbb'),('ccc'),('ddd')) as v(name) 
on ude.people_category=v.name 
group by v.name;

Answer 1

The PostgreSQL VALUES function is not supported by querydsl. However, you can get the same result using a UNION.

CREATE TABLE personal_progress_entity(student_entity_id INTEGER);
INSERT INTO personal_progress_entity VALUES (1);

CREATE TABLE user_detail_entity(user_id INTEGER, people_category VARCHAR);
INSERT INTO user_detail_entity VALUES (1, 'aaa');
INSERT INTO user_detail_entity VALUES (1, 'bbb');

SELECT COUNT(personal_progress_entity.student_entity_id),
       name.value_alias
FROM personal_progress_entity personal_progress_entity
LEFT JOIN user_detail_entity user_detail_entity ON personal_progress_entity.student_entity_id = user_detail_entity.user_id
RIGHT JOIN ((SELECT 'aaa' AS value_alias)
             UNION
            (SELECT 'bbb' AS value_alias)
             UNION
            (SELECT 'ccc' AS value_alias)
             UNION
            (SELECT 'ddd' AS value_alias)) AS name
ON name.value_alias = user_detail_entity.people_category
GROUP BY name.value_alias;

Gives:

1 "aaa"
1 "bbb"
0 "ddd"
0 "ccc"

Here's my querydsl-sql implementation. I've added the private static <T> Union<T> union(PathBuilder<T> pathBuilder, T... values) method to reduce boilerplate.

public List<Tuple> stackoverflowAnswer() {
    PathBuilder<String> valueAlias = new PathBuilder<>(String.class, "value_alias");
    PathBuilder<String> name = new PathBuilder<>(String.class, "name");
    return query().select(personalProgressEntity.studentEntityId.count(), name.get(valueAlias))
            .from(personalProgressEntity)
            .leftJoin(userDetailEntity).on(personalProgressEntity.studentEntityId.eq(userDetailEntity.userId))
            .rightJoin(union(valueAlias, "aaa", "bbb", "ccc", "ddd"), name).on(name.get(valueAlias).eq(userDetailEntity.peopleCategory))
            .groupBy(name.get(valueAlias))
            .fetch();
}

private static <T> Union<T> union(PathBuilder<T> pathBuilder, T... values) {
    return SQLExpressions.union(
            Stream.of(values)
                    .map(p -> SQLExpressions.select(Expressions.constantAs(p, pathBuilder)))
                    .collect(Collectors.toList()));
}