JPQL group by user id and make other column results arranged to a list/array

Question

I have a working query:

SELECT p.user.id, p.id FROM Photo p WHERE p.user.id IN ?1 GROUP BY p.user.id, p.id

This returns data like

2, 656
2, 767
2, 788
6, 986
6, 1364
etc...

But it want it like this:

2; 656,767,788
6; 986,1364...

So for each user, the p.user.id, and then the list of p.id. Preferably in formatted string, comma separated, as it will be sent as json.

score 1 · Accepted Answer · answered Jun 30 '20 at 21:12

1

SELECT p.user.id, GROUP_CONCAT(p.id SEPARATOR ', ') from Photo p GROUP BY p.user.id;

or if you want absolutely the semicolon

SELECT concat(p.user.id,';'), GROUP_CONCAT(p.id SEPARATOR ', ') from Photo p GROUP BY p.user.id;

answered Jun 30 '20 at 21:12

n2ad

2

Thanks! You gave some hints. This exactly isn't worked, as Group_concat is not the part of JPQL, but with this trick https://stackoverflow.com/a/62410812/1629074 it worked, however separator SEPARATOR ', ' is not accepted, but anyway it uses , to separate the element. – Jun 30 '20 at 21:51

1 Answers1