0

I have 3 tables: user, privilege and user_privilege. Last table stores user_id and privilege_id, i.e. information about users and their privileges. I want to filter users by privileges. For this, I am passing list of privilege ID's List<Integer> privilegeIDs as parameter to the query below:

@NamedNativeQuery(name = "User.filterUsersByPrivilges",
        query = "SELECT u.username " +
                "FROM user u " +
                "LEFT JOIN user_privilege up ON u.id = up.user_id " +
                "WHERE up.privilege_id IN ?1; ",
        resultSetMapping = "userCatalog")

However, on client side (Angular 5), sometimes I may leave this privilege list empty in order to retrieve all users, no matter if they have a privilege or not. Here I have found out that if I pass empty list parameter to IN condition, it gives an error in server logs. It turns out when checking with the IN condition, parameter cannot be empty.

I have tried to pass null and check with WHERE up.privilege_id IN ?1 OR ?1 IS NULL but it still swears that you can not pass an empty list to IN.

Now, the question is: how I must check if list is empty so that I can retrieve all users even if they have or do not have privileges; at the same times, if parameter is not empty - retrieve those users that has privileges specified in the list parameter.

If you have experience in solving a similar problem, help me, please. Thanks in advance!

Zhandos Ainabek
  • 11
  • 2
  • How about checking if the parameter is present previously and if it is not call a different query? – NiVeR Jun 09 '18 at 17:40
  • @NiVeR I have also thought about it. However, I have simplified my query in this post. In fact, I have three 'LEFT JOIN's: user_privilege, user_competition, user_hobby. So, I don't think that I should write 8 different queries :D – Zhandos Ainabek Jun 09 '18 at 17:44
  • Try Spring Data Specification! – Prakash Ayappan Jun 09 '18 at 17:59

1 Answers1

1

I have found solution for my problem. On client side (Angular 5), before sending my privilege list, I check if it is empty or not. If there is no data in the list, I am sending my privilege filter list as following: [0].

At the same time, I am changing my query like this:

@NamedNativeQuery(name = "User.filterUsersByPrivilges",
        query = "SELECT u.username " +
                "FROM user u " +
                "LEFT JOIN user_privilege up ON u.id = up.user_id " +
                "WHERE up.privilege_id IN ?1 OR (0 IN ?1 AND 1=1); ",
        resultSetMapping = "userCatalog")

With 0 IN ?1 AND 1=1 I identify if this list is empty and retrieving all users no matter if they have privileges or not.

Zhandos Ainabek
  • 11
  • 2