Native User Query issue

Camunda 7 Topics Discussion & Questions
1

Hi!
After executing a createNativeUserQuery it returns a correct list size but with null object.

For instance:

String sql = “select * from act_id_membership where GROUP_ID_ = #{groupId}”;
List list = identityService.createNativeUserQuery().sql(sql).parameter(“groupId”, groupId).list();

Result is a list lenght 4 (as records in th table) but are all null object :frowning:
What am I doing wrong?Is there a way to query memeberhip table?

2

I think the list() on a native user query returns a list of User. Hence you should probably select * from the underlying user table (I guess its act_id_user but managementService.getTableName(User.class) will tell you). I haven’t tested it but something like that might work:

String sql = "select * from "+ managementService.getTableName(User.class) +" u where u.id_ in (select user_id_ from act_id_membership where group_id_ = #{groupId}) "  ;
List<User> list = this.identityService.createNativeUserQuery().sql(sql).parameter("groupId", groupId).list();
3

Hi @f_a
as anticipated I was in error, below the fixed query :wink:

select * from act_id_user aiu
inner join act_id_membership aim on aim.USER_ID_ = aiu.ID_
where aim.GROUP_ID_ = #{groupId}
and …

Thanks a lot, regards