How to retrieve Users, their AssignedPermisionSets and Public Groups they are part of using a SOQL query

Question

I have a use case where I have to query Users, their AssignedPermisionSets and Public Groups they are part of using a single SOQL query in an Excel File, I'm currently using Enabler4Excel for this purpose. I can retrieve the data in workbench individually writing querries for PermissionSetAssignment and Groups which is a piece of cake.

However, I'm unable to create a single query that can retrieve data in 3 columns namely User Name, Permission Sets and Public Groups. A single user can have multiple Permission Sets and Public Groups thus they should be in a single row with comma or semi-colon separated, with just one row created for each unique user.

Answer 1

You can't do that in SOQL. Assigned Permission Sets and Public Group memberships are different children of the User.

While you can write multiple subqueries to fetch child records down different relationship paths, SOQL returns you structured data: for each User record, in JSON, you obtain a related list of records for each child relationship. SOQL does not perform joins the way SQL does to yield a flat list, and does not support concatenating child relationship fields into parent rows.

You'll have to postprocess your data somehow in order to get this outcome.