Search code examples
javasqljpajpql

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

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.

Solution 
  • 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;