Mixing
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 '18 at 8:36
Billy Frost
1,74598
asked Nov 20 '18 at 6:27
xz8885xz8885
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 '18 at 8:36
Billy Frost
1,74598
asked Nov 20 '18 at 6:27
xz8885xz8885
82
yes, you CAN!!!
– Sarief
Nov 20 '18 at 6:37
You can't but you can get the same result. See my answer for further details.
– Robert Bain
Nov 30 '18 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 '18 at 8:36
Billy Frost
1,74598
asked Nov 20 '18 at 6:27
xz8885xz8885
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 '18 at 8:36
Billy Frost
1,74598
asked Nov 20 '18 at 6:27
xz8885xz8885
82
edited Nov 20 '18 at 8:36
Billy Frost
1,74598
asked Nov 20 '18 at 6:27
xz8885xz8885
82
edited Nov 20 '18 at 8:36
Billy Frost
1,74598
edited Nov 20 '18 at 8:36
Billy Frost
1,74598
edited Nov 20 '18 at 8:36
Billy Frost
1,74598
1,74598
asked Nov 20 '18 at 6:27
xz8885xz8885
82
asked Nov 20 '18 at 6:27
xz8885xz8885
82
asked Nov 20 '18 at 6:27
xz8885xz8885
82
82
yes, you CAN!!!
– Sarief
Nov 20 '18 at 6:37
You can't but you can get the same result. See my answer for further details.
– Robert Bain
Nov 30 '18 at 20:39
add a comment |
yes, you CAN!!!
– Sarief
Nov 20 '18 at 6:37
You can't but you can get the same result. See my answer for further details.
– Robert Bain
Nov 30 '18 at 20:39
yes, you CAN!!!
– Sarief
Nov 20 '18 at 6:37
yes, you CAN!!!
– Sarief
Nov 20 '18 at 6:37
You can't but you can get the same result. See my answer for further details.
– Robert Bain
Nov 30 '18 at 20:39
You can't but you can get the same result. See my answer for further details.
– Robert Bain
Nov 30 '18 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 '18 at 20:38
Robert BainRobert Bain
3,63742039
It works! Thank you very much!
– xz8885
Dec 1 '18 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 '18 at 20:38
Robert BainRobert Bain
3,63742039
It works! Thank you very much!
– xz8885
Dec 1 '18 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 '18 at 20:38
Robert BainRobert Bain
3,63742039
It works! Thank you very much!
– xz8885
Dec 1 '18 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 '18 at 20:38
Robert BainRobert Bain
3,63742039
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 '18 at 20:38
Robert BainRobert Bain
3,63742039
answered Nov 30 '18 at 20:38
Robert BainRobert Bain
3,63742039
answered Nov 30 '18 at 20:38
Robert BainRobert Bain
3,63742039
answered Nov 30 '18 at 20:38
Robert BainRobert Bain
3,63742039
3,63742039
It works! Thank you very much!
– xz8885
Dec 1 '18 at 13:58
add a comment |
It works! Thank you very much!
– xz8885
Dec 1 '18 at 13:58
It works! Thank you very much!
– xz8885
Dec 1 '18 at 13:58
It works! Thank you very much!
– xz8885
Dec 1 '18 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.
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 '18 at 6:37
You can't but you can get the same result. See my answer for further details.
– Robert Bain
Nov 30 '18 at 20:39