时间戳

首页 > 解决方案 > Querydsl——使用 postgresql 的值

sql - Querydsl——使用 postgresql 的值

问题描述

我可以把这个 sql 翻译成 querydsl 形式吗？

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;

标签： sqlquerydsl

解决方案

VALUESquerydsl 不支持PostgreSQL函数。但是，您可以使用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;

给出：

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

这是我的querydsl-sql实现。我添加了private static <T> Union<T> union(PathBuilder<T> pathBuilder, T... values)减少样板的方法。

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()));
}

推荐阅读