JPA JPQL request with select and Concat

[xael-fry]

I have in java differents class

@Entity
@Table(name = "users")
public class User extends Model {
  public String name;

  @ManyToMany
  public List<Role> roles;
}

And

@Entity
@Table(name = "roles")
public class Role extends Model {
  public String name;
}

Tables contains

Table Users    |        |   Table Roles   |           |Table Users_roles    |
id    | Name   |        | id    | Name    |           |users_id  | roles_id |
1     | User 1 |        | 1     | CEO     |           |    2     |    1     |
2     | User 2 |        | 2     | Manager |           |    2     |    2     |
3     | User 3 |                                      |    3     |    1     |

I want to make the following request (ok with PostgreSql 9.1.12)

SELECT u.*, array_to_string(array_agg(r.name ORDER BY r.name ASC), ' / ')
  FROM  users u
  LEFT OUTER JOIN users_roles ur ON u.id=ur.users_id
  FULL JOIN roles r ON r.id=ur.roles_id
  GROUP BY u.id, ur.users_id
  ORDER BY array_to_string ASC

I have the result I Want

User name | roles
User 1    |          
User 3    | CEO        
User2     | CEO / MANAGER         

Can you help me to get the JPA syntax for this request?

[christian sarnataro]

Hi, you could try with a native query.

See this thread:

https://groups.google.com/forum/#!searchin/play-framework/RAW$20sql/play-framework/S06v0trnaus/cjSZER43amcJ

I think that native queries can be useful when you want to get some "custom" data.

[tazmaniac]

I needed to do something similar to access MySQL group_concat function from within JPA. And to do that I did the following:

1. Implement org.hibernate.dialect.function.SQLFunction to render the group_concat function:

public String render(Type firstArgumentType, List arguments, SessionFactoryImplementor factory)

throws QueryException {

if (arguments.size() < 1) {

throw new QueryException(new IllegalArgumentException("group_concat should have at least one arg"));

}

StringBuilder builder = new StringBuilder();

if (arguments.size() > 1 && arguments.get(0).equals("'distinct'")) {

builder.append("distinct ");

builder.append(arguments.get(1));

} else {

builder.append(arguments.get(0));

}

return "group_concat(" + builder.toString() + ")";

}

2. Extend MySQL5InnoDBDialect to register the group_concat function:

public CustomMySQL5InnoDBDialect() {

super();

registerFunction("group_concat", new GroupConcatFunction());

}

3. Update application.conf to use the custom Hibernate dialect:

jpa.dialect=utils.CustomMySQL5InnoDBDialect