Hello,

I am trying to create an aggregate or SQL statement that will return all of the Groups that a user is not already apart of. 

On the UserDetail screen, I have a GroupUser table, listing all of the groups that that user is in. There is a link to another screen to add that specific user to additional groups. 

I am not very familiar with SQL, and I think you need SQL for what I'm trying to do. I've tried using an aggregate with the filter of "group_user.user_id<>UserId", but the aggregate still returns groups with group_users of other userId's.


Please tell me if I can clarify more, and I appreciate your help! 

Solution

Hi Andi,

You can accomplish this with and aggregate with the entities Group and Group_User. Join them like this "Group" With or without "Group_user" and the this condition:

SyntaxEditor Code Snippet

Group.Id = Group_User.Group_Id and Group_User.User_Id = UserId

Just finish it by filtering by the ones which Group_User.Id = NullIdentifier()

Hope this helps.

Regards,

Marcelo

Solution

You should be able to do this with an aggregate.

The basics are you need to have an aggregate with both the Group and Group_User entities from System (you will probably need to add these using the Manage Dependencies window).

Start by creating a Preparation for your screen, and dragging the Group entity onto the flow. Then double-click and add a new source, using Group_User as the additional source. The default join should work for this.

Then add the filter:

Group_User.User_Id <> GetUserId()

This should return all groups that the logged in user is not a member of. If you want to get groups for an arbitrary user, you would use an input parameter with the user ID of that user, instead of GetUserId().

From what you describe above, it sounds like this is what you've tried, and I would expect it to return groups for other user IDs, since those groups might not include the desired user.

If the above doesn't help, please share a small OML that reproduces the issue. That might help with troubleshooting.

Marcelo Ferreira wrote:

Hi Andi,

You can accomplish this with and aggregate with the entities Group and Group_User. Join them like this "Group" With or without "Group_user" and the this condition:

SyntaxEditor Code Snippet

Group.Id = Group_User.Group_Id and Group_User.User_Id = UserId

Just finish it by filtering by the ones which Group_User.Id = NullIdentifier()

Hope this helps.

Regards,

Marcelo

Thank you so much! It's that filter I was missing. You rock!


Hi All,

I had started working on a different answer to this, as I understood the question, you want to get a list of the groups that a given user is not part of.


If I'm reading the solution of Marcelo correctly, this will only return groups that no users are a member of, so as soon as any other user is in that group, it will not show up anymore for selection.

And I think Andrew's solution will still show groups that the current user is in, if at least one other user is also in it.


With SQL, this use case can be solved with for example a NOT EXISTS clause, I haven't looked into recreating this with an aggregate, probably not straightforward, so I think SQL is the simpler solution here.


SyntaxEditor Code Snippet

SELECT      *
FROM        {Group}
WHERE       NOT EXISTS
            (
                SELECT      *
                FROM        {Group_User}
                WHERE       {Group_User}.[User_Id] = @InUserId
                AND         {Group_User}.[Group_Id] = {Group}.[Id]
            )



Dorine



Ok,


so out of curiosity, I tried to concoct an aggregate that would do the same.

It is possible, but I still think the SQL is the simpler solution and probably the faster.


so basically, here's how you could do it with an aggregate :

1) sources are group with or without group_user

2) add calculated column with formula 

If(Group_User.User_Id = varUserId and varUserId <> NullIdentifier(), 1, 0)

3) add groupby on columns group id and group name, and max on this new calculated column

4) add filter on the maxed column 

UserIsInGroupMax = 0


as you see, very convoluted, so only to be used by those for who using SQL goes against their deepest beliefs.


See attached oml

Dorine

Hi Dorine,

I think you are over complicating. My solution works because I'm not joining with all the records of Group_User. I'm only left joining with the ones that belong to the current user 

and Group_User.User_Id = UserId

Give it a go and let me know if you have any doubts.

Regards,

Marcelo

Marcelo Ferreira wrote:

Hi Dorine,

I think you are over complicating. My solution works because I'm not joining with all the records of Group_User. I'm only left joining with the ones that belong to the current user 

and Group_User.User_Id = UserId

Give it a go and let me know if you have any doubts.

Regards,

Marcelo

Ah, oke Marcelo,


I see what you are doing now, very neat solution, I'll think of it in the future before jumping to SQL too quickly...


I never used conditions in a JOIN clause before (other that the columns to join on), so thank you, a new tool in my toolbox.