Skip to content

I want to get an index of users who belong to a Pleasanter group

Answer

Please use the following method to obtain the information..

  1. Use the "Group Retrieval API"
  2. Use the script "$p.apiGroupsGet"
  3. Use the server scripts "groups.Get" and "group.GroupMembers"
  4. Use SQL. Explained on this page.

Overview

This is a sample SQL statement to obtain an index of "User" belonging to a "Group" in Pleasanter.

Procedure

Use SQL Server Management Studio or Azure Data Studio to connect to the Pleasanter database and execute the following SQL. The following SQL is compatible with both SQL Server and PostgreSQL.

Code Samples

select 
    "Groups"."GroupId"
    ,"Groups"."GroupName"
    ,"Users"."UserId"
    ,"Users"."LoginId"
    ,"Users"."Name"
from "Groups" inner join "GroupMembers" on "Groups"."GroupId" = "GroupMembers"."GroupId"
    inner join "Users" on "GroupMembers"."UserId" = "Users"."UserId"
-- If you delete a group under the union, users via the department will not be retrieved.
union
select 
    "Groups"."GroupId"
    ,"Groups"."GroupName"
    ,"Users"."UserId"
    ,"Users"."LoginId"
    ,"Users"."Name"
from "Groups" inner join "GroupMembers" on "Groups"."GroupId" = "GroupMembers"."GroupId"
    inner join "Depts" on "GroupMembers"."DeptId" = "Depts"."DeptId"
    inner join "Users" on "Depts"."DeptId" = "Users"."DeptId"

Image of Execution Result

The SQL execution result, listing the users who belong to a group