How to dynamically extend a WHERE clause in apex

Question

I need to query a list of opportunities where AccountId = AccountId AND OwnerId = UserInfo.getUserId, and if that logged-in user has a higher role(manager), they can see their team members' opportunities, but if that user is a member, they can only see their own opportunity. 

Now my query is:

select id, name, stagename, closedate, amount from opportunity where AccountId = :AccountId

I want the query to be like this:

select id, name, stagename, closedate, amount from opportunity where AccountId =: AccountId and (OwnerId =: userId or OwnerId =: teamMember1_Id or OwnerId =:teamMember2_Id or... );

The OR condition in the OwnerId will keep extending unless that user is a team member. I have a list of OwnerId, but I don't know how to add them to a query using Apex. Please help.

FYI: This query will show opportunities on the Visualforce page based on which user is logged in.

Answer 1

I am assuming logged in user should see the data owned by them and users below them in the hierarchy. If the APEX class is using "WITH SHARING" keyword then sharing rules are enforced and this will take care of what data the logged in user can see. I think you only need the AccountId clause. OwnerId clause is not needed.

But if I am not understanding correctly then you can do dynamic SOQL. something like this:

String queryStr = 'select id, name, stagename, closedate, amount from opportunity where AccountId =: AccountId and ';

for(){   
   // Build you OwnerId logic clause here
   queryStr = queryStr + 'OwnerId =: userId or '
}
// finally run the dynamic query
List<sObject> sobjList = Database.query(queryStr);

Answer 2

Use an if condition to modify the query based on the UserInfo.getUserId.

Or, you could try this Query

SELECT Id, Name, StageName, CloseDate, Amount, 
Owner.Name, 
Owner.UserRole.DeveloperName, 
Owner.UserRole.Name
FROM Account
WHERE Id = :AccountId
AND OwnerId = :userId

And then filter the records based on the role.

Answer 3

I would suggest a more optimal solution would be to use a list/set for the where clause:

Set<Id> accountIds = new Set<Id>();
Set<Id> ownerIds = new Set<Id>();

accountIds.add(...);
ownerIds.add(...);

[SELECT Id, Name, StageName, CloseDate, Amount,
    Owner.Name,
    Owner.UserRole.DeveloperName,
    Owner.UserRole.Name
FROM Account
WHERE Id IN :accountIds
AND OwnerId IN :ownerIds
LIMIT 10000];

This will then help avoid limit of the query being too long if the team is large.

Answer 4

As you specified in your question you have list of OwnerIds, lets call it as ownerIdsList .

String ownerIds = '';
for(Id ownerId : ownerIdsList) {
    ownerIds += '\'' + String.escapeSingleQuotes(ownerId) + '\',';
}
ownerIds = ownerIds.removeEnd(',');

String query = 'SELECT Id, Name, StageName, CloseDate, Amount FROM Opportunity WHERE AccountId = :AccountId AND (OwnerId IN (' + teamMemberIds + '))';
List<Opportunity> opportunities = Database.query(query);

The Database.query() method is a dynamic SOQL query, which means that you can construct the query string at runtime, based on some input criteria.