Question around SOQL query on a Task and why I can't use whoid as a where clause

Question

This query works for me on the Task object. I am able to show WhoId as field

select id, subject, WhoId, Who.name, who. createddate  from task where owner.id ='0056F00000DWfjYQAT' and subject like 'outbound%'order by createddate desc

However wanted to narrow it down further by using the WhoId as a WHERE clause but that didn't work. I tried

select id, subject, WhoId, Who.name, who. createddate  from task where owner.id ='0056F00000DWfjYQAT' and subject like 'outbound%' and whoid like '00Q%'order by createddate desc

This is the error message I got

invalid operator on id field

Would like to understand why I can't do that? How can I rewrite my query to narrow down my search?

Thank you

Answer 1

From what you are doing in your query, I am guessing you are trying to narrow down the query to only include Tasks where the Who is a Lead.

Instead of trying to compare the Id prefix, you need to use a polymorphic query. It's going to be something along the lines of:

SELECT Id, Subject, WhoId,
        TYPEOF Who WHEN Lead THEN Name, CreatedDate END
    FROM Task
    WHERE OwnerId = '0056F00000DWfjYQAT'
        AND Subject LIKE 'outbound%'
        AND Who.Type = 'Lead'
    ORDER BY CreatedDate DESC

Note that, as per the TYPEOF documentation, the fields after the THEN and before the END are fields from the Who, not from the Task.

This query may be a bit "belt and braces"; I'm not 100% sure you need the AND Who.Type = 'Lead' part of the WHERE clause, but logically you probably do.

Answer 2

As per salesforce documentation for SOQL.

The LIKE operator is supported for string fields only.

You have to query without whoid like filter and then filter using for loop.

   List<Task> tasksFilter1 = [select id, subject, WhoId, Who.name  from task where owner.id ='0056F00000DWfjYQAT' and subject like 'outbound%' order by createddate desc];
List<Task> tasksFilter2 = new List<Task>();
for(Task t : tasksFilter1 ) {
     if(t.whoid !=null && String.valueOf(t.whoid).startsWith('00Q')) {
         tasksFilter2.add(t);
     }
}
System.debug('Filtered result : '+tasksFilter2 );