CODEWITHSUNDEEP
javaselectjpaconcatenation
Alex
Alex

Reputation: 101

JPA JPQL request with select and Concat

I have two entities 

@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 query (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        
User 2    | CEO / MANAGER

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

Upvotes: 0

Views: 7236

Answers (3)

Alex
Alex

Reputation: 101

I can do this request by using :

String jpql = "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 DESC" ;

Query query = Model.em().createNativeQuery(jpql);
scManager.bindParameters(query);

List<Object[]> searchCriteriaUsers = new JPAQuery(jpql, query).fetch();

The request is ok, and result. But I was looking for a JPQL query to order the list by roles name...

Upvotes: 0

James
James

Reputation: 18379

If you just want the data, then just use a native SQL query to execute the SQL you have.

If you want objects, then just select the User objects and access their roles in Java to collect up the data. To optimize the query to roles, you could join fetch it, or batch fetch it.

Upvotes: 0

JB Nizet
JB Nizet

Reputation: 691785

No way to do that directly with a JPQL query. But you could just have a method in the User entity which returns the String of role names, and fetch the users with their roles:

List<User> users = em.createQuery("select u from User u left join fetch u.roles")
                     .getResultList();

and, using Guava's Joiner, in User.java:

public String getUserNames() {
    List<String> roleNames = new ArrayList<String>();
    for (Role role : roles) {
        roleNames.add(role.getName();
    }
    return Joiner.on(" / ").join(roleNames);
}

Upvotes: 1

Related Questions