Is there an alternative for native query's 'GROUP_CONCAT' in JPQL with JPA?

Question

I am trying to fetch profile menus of a profile using jpql with JPA. My 'Profile' and 'ProfileMenus' entities have many to one relationship.

I have tried looking into these answers but couldn't find any working solution.

How to add non-standardized sql functions in Spring Boot application?

Registering a SQL function with JPA and Hibernate

https://vladmihalcea.com/hibernate-sql-function-jpql-criteria-api-query/

I also went through this link and seems to have same problem as mine, How to register non-standarized SQL functions manually in Spring Boot application?

When using native query I can fetch my data using the query below:

 SELECT
 GROUP_CONCAT(pm.user_menu_id SEPARATOR ',')
 AS profile_menu_ids,
 p.description
 FROM profile p
 LEFT JOIN profile_menu pm ON p.id = pm.profile_id
 WHERE
 p.id =:profileId
 AND
 pm.status = 'Y'
 GROUP BY p.id

The above query provides me data as,

profile_menu_ids	description
4,5	admin profile

Is there any way or alternatives in jpql with JPA to obtain result as above?

`GROUP_CONCAT` is a MySQL (and SQLite) specific function, so I doubt JPQL would support it. You might have to handle this on the Java side, perhaps using streams. — Tim Biegeleisen, Aug 07 '19 at 11:47
https://stackoverflow.com/questions/7005354/jpa-criteria-api-group-concat-usage — Tirex, Aug 07 '19 at 11:51
I actually want to handle it using JPQL. Isn't there alternative in JPQL? @TimBiegeleisen — Sabu Shakya, Aug 08 '19 at 11:32
No, I don't think there is, mainly because every database vendor has a different way of handling group concatenation (there probably is an ANSI SQL way of doing it, but my guess is that few/none of the vendors actually support it). — Tim Biegeleisen, Aug 08 '19 at 11:33

score 0 · Answer 1 · answered Aug 13 '19 at 15:13

You may consider using FluentJPA, which supports any custom function:

public ProfileMenuGroup getMenuIdsByProfile(int profileId) {
    FluentQuery query = FluentJPA.SQL((Profile p,
                                       ProfileMenu pm) -> {
        String menuIds = alias(GROUP_CONCAT(pm.getUserMenuId(), ","),
                                            ProfileMenuGroup::getProfileMenuIds);
        String description = alias(p.getDescription(), ProfileMenuGroup::getDescription);

        SELECT(menuIds, description);
        FROM(p).LEFT_JOIN(pm).ON(p == pm.getProfile());
        WHERE(p.getId() == profileId && pm.getStatus() == "Y");
        GROUP(BY(p.getId()));
    });
    return query.createQuery(em, ProfileMenuGroup.class).getSingleResult();
}

the query produces the following SQL (profileId is auto bound):

SELECT GROUP_CONCAT(t1.user_menu_id SEPARATOR ',') AS profile_menu_ids,
                                    t0.description AS description 
FROM profile t0  LEFT JOIN profile_menu t1  ON (t0.id = t1.profile_id) 
WHERE ((t0.id = ?1) AND (t1.status = 'Y')) 
GROUP BY  t0.id

Given the following type declarations:

@Entity
@Data // lombok
@Table(name = "profile")
public class Profile {
    @Id
    private int id;

    private String description;
}

@Entity
@Data // lombok
@Table(name = "profile_menu")
public class ProfileMenu {
    @Id
    private int id;

    @ManyToOne
    @JoinColumn(name = "profile_id")
    private Profile profile;

    private int userMenuId;

    private String status;
}

@Tuple
@Data // lombok
public class ProfileMenuGroup {

    private String profileMenuIds;

    private String description;
}

Is there an alternative for native query's 'GROUP_CONCAT' in JPQL with JPA?

1 Answers1