Querydsl--using postgresql's values
0
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;
sql querydsl
edited Nov 20 at 8:36
Billy Frost
1,74698
asked Nov 20 at 6:27
xz8885
82
add a comment |
0
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;
sql querydsl
edited Nov 20 at 8:36
Billy Frost
1,74698
asked Nov 20 at 6:27
xz8885
82
yes, you CAN!!!
– Sarief
Nov 20 at 6:37
You can't but you can get the same result. See my answer for further details.
– Robert Bain
Nov 30 at 20:39
add a comment |
0
0
0
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;
sql querydsl
edited Nov 20 at 8:36
Billy Frost
1,74698
asked Nov 20 at 6:27
xz8885
82
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;
sql querydsl
sql querydsl
edited Nov 20 at 8:36
Billy Frost
1,74698
asked Nov 20 at 6:27
xz8885
82
edited Nov 20 at 8:36
Billy Frost
1,74698
asked Nov 20 at 6:27
xz8885
82
edited Nov 20 at 8:36
Billy Frost
1,74698
edited Nov 20 at 8:36
Billy Frost
1,74698
edited Nov 20 at 8:36
Billy Frost
1,74698
1,74698
asked Nov 20 at 6:27
xz8885
82
asked Nov 20 at 6:27
xz8885
82
asked Nov 20 at 6:27
xz8885
82
82
yes, you CAN!!!
– Sarief
Nov 20 at 6:37
You can't but you can get the same result. See my answer for further details.
– Robert Bain
Nov 30 at 20:39
add a comment |
yes, you CAN!!!
– Sarief
Nov 20 at 6:37
You can't but you can get the same result. See my answer for further details.
– Robert Bain
Nov 30 at 20:39
yes, you CAN!!!
– Sarief
Nov 20 at 6:37
yes, you CAN!!!
– Sarief
Nov 20 at 6:37
You can't but you can get the same result. See my answer for further details.
– Robert Bain
Nov 30 at 20:39
You can't but you can get the same result. See my answer for further details.
– Robert Bain
Nov 30 at 20:39
add a comment |
1 Answer
1
active
oldest
votes
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()));
}
answered Nov 30 at 20:38
Robert Bain
3,60742039
It works! Thank you very much!
– xz8885
Dec 1 at 13:58
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53387407%2fquerydsl-using-postgresqls-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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()));
}
answered Nov 30 at 20:38
Robert Bain
3,60742039
It works! Thank you very much!
– xz8885
Dec 1 at 13:58
add a comment |
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()));
}
answered Nov 30 at 20:38
Robert Bain
3,60742039
It works! Thank you very much!
– xz8885
Dec 1 at 13:58
add a comment |
1
1
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()));
}
answered Nov 30 at 20:38
Robert Bain
3,60742039
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()));
}
answered Nov 30 at 20:38
Robert Bain
3,60742039
answered Nov 30 at 20:38
Robert Bain
3,60742039
answered Nov 30 at 20:38
Robert Bain
3,60742039
answered Nov 30 at 20:38
Robert Bain
3,60742039
3,60742039
It works! Thank you very much!
– xz8885
Dec 1 at 13:58
add a comment |
It works! Thank you very much!
– xz8885
Dec 1 at 13:58
It works! Thank you very much!
– xz8885
Dec 1 at 13:58
It works! Thank you very much!
– xz8885
Dec 1 at 13:58
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53387407%2fquerydsl-using-postgresqls-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
yes, you CAN!!!
– Sarief
Nov 20 at 6:37
You can't but you can get the same result. See my answer for further details.
– Robert Bain
Nov 30 at 20:39