Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
BenzyBenzy 

Get SOQL query to return different results based on current user profile/role

Hi,

I currently have a SOQL query to populate a dynamic picklist (see below). Notice that I am already filtering the list results based on the city of the current user matching that of the users returned by the query. 

Id Mgrid = [select Id,ManagerId from User where id =: userinfo.getuserid()].ManagerId;
string MgrCity = [select City from User where id =: Mgrid].city;

list<user> u = new list<user>();
u = [SELECT Id, Name FROM User WHERE City =: MgrCity AND Profile.Name = 'Pollinators' AND IsActive = TRUE ORDER BY Name ASC];

The purpose of this is so different city managers can see a list of only their team members. However, I want people above city managers (eg the admin) to see the same list but without the City condition.

Any idea how to do that?
Thanks
pconpcon
I would split it into two different queries based on profile
  
Set<String> seeAllProfiles = new Set<String> {
    'System Administrator',
    'Super Manager'
}

User currentUser = [
    select ManagerId,
        Profile.Name
    from User
    where id = :userinfo.getuserid()
];

User manager = [
    select City
    from User
    where id = :currentUser.ManagerId
];

List<User> users = new List<User>();

if (seeAllProfiles.contains(currentUser.Profile.Name)) {
    users = [
        select Name
        from User
        where Profile.Name = 'Pollinators' and
            IsActive = true
        order by Name asc
    ];      
} else {
    users = [
        select Name
        from User
        where City = :manager.City and
            Profile.Name = 'Pollinators' and
            IsActive = true
        order by Name asc
    ];
}

 
BenzyBenzy
Thanks pcon

That didn't quite work. I keep getting an error about the 'if' part. See below the full code I'm currently using to get the dynamic picklist to populate:

Id UserId = [select Id,ManagerId from User where Id =: userinfo.getUserId()].Id;
    public string UserCity = [select City from User where Id =: UserId].City;

    public List<SelectOption> getpollinatornames()
        {
          List<SelectOption> options = new List<SelectOption>();
          List<User> pollinatorlist = new List<User>();
          pollinatorlist = [Select Id, Name FROM User ];
          options.add(new SelectOption('--None--','--None--'));
          for (User users : [SELECT Id, Name FROM User WHERE City =: UserCity AND Profile.Name = 'Pollinators' AND IsActive = TRUE ORDER BY Name ASC]) 
              {
                  options.add(new selectOption(users.Name, users.Name));
              }
      return options;
        }

I realise now this is a bit more complex than I thought! Here is my attempt at incorporating your split queries into this. Any idea why it's not working? (I made a few small changes to the beginning to simplify it).

Id UserId = [select Id,ManagerId from User where Id =: userinfo.getUserId()].Id;
    public string Profiles = 'System Administrator';
    public string UserCity = [select City from User where Id =: UserId].City;
    public string UserProfile = [select Profile.Name from User where Id =: UserId].Profile.Name;

    public List<SelectOption> getpollinatornames()
        {
          List<SelectOption> options = new List<SelectOption>();
          List<User> pollinatorlist = new List<User>();
          pollinatorlist = [Select Id, Name FROM User ];
          options.add(new SelectOption('--None--','--None--'));
          for (User 
              if (seeAllProfiles.contains(UserProfile)) {
            users : [SELECT Name FROM User WHERE Profile.Name = 'Pollinators' AND IsActive = true ORDER BY Name ASC];      
            } else {
            users : [SELECT Name FROM User WHERE City = :UserCity AND Profile.Name = 'Pollinators' AND IsActive = true ORDER BY Name ASC];
            }
          ) 
              {
                  options.add(new selectOption(users.Name, users.Name));
              }
      return options;
        }

Thanks
pconpcon
This should be what you want.  I updated the code to reduce the number of redundant queires.  I'm assuming that you are doing this in a controller or controller extension, so I've mocked it up.
  
public class myController {
    public static List<String> seeAllProfiles = new Set<String>{
        'System Administrator'
    };

    private User currentUser; 

    public myController() {
        this.currentUser = [
            select City,
                Profile.Name
            from User
            where Id = :UserInfo.getUserId()
        ];
    }

    public List<SelectOption> getPollinatorNames() {
        String userCity = currentUser.City;
        String userProfile = currentUser.Profile.Name;

        List<SelectOption> options = new List<SelectOption>{
            new SelectOption('--None--', '')
        };

        List<User> pollinatorList = new List<User>();

        if (seeAllProfiles.contains(userProfile)) {
            pollinatorList = [
                select Name
                from User
                where Profile.Name = 'Pollinators' and
                    IsActive = true
                order by Name asc
            ];
        } else {
            pollinatorList = [
                select Name
                from User
                where City = :userCity and
                    Profile.Name = 'Pollinators' and
                    IsActive = true
                order by Name asc
            ];
        }

        for (User u: pollinatorList) {
            options.add(new selectOption(u.Name, u.Id));
        }

        return options;
    }
}

Next time you add code, please use the "Add a code sample" button (icon <>) to help increase the readability.
BenzyBenzy
Hi pcon

Thanks. But I keep running into problems with the "if". When trying to save the page, it always throws an error for the 'if'. Maybe a silly question but are you sure it's possible to put if/else in an SOQL statement?
pconpcon
There is no if statement inside of the SOQL statement. What error exactly are you getting?  And is it when you save the "page" or when you save the controller?  Also, if you have made any changes to the code that is listed above, please include all of the changed code.

If the error message contains a line number that does not coorilate with the line numbers in the code above, please let me know which line in the code above the error is occuring.