Temp table or table type in SOQL

Question

I have an apex remote action. Inside of it, I want to make an SOQL query. I want to create a query that is as close as SQL:

SELECT Id, Text1__c, Text2__c FROM MyCustomObj__c t1 JOIN #TempTable t2 ON t1.Text1__c = t2.Text1 AND t1.Text2__c = t2.Text2

However, in SOQL, I don't have a temp table like #TempTable in SQL. The query in SOQL I can write is something like:

SELECT Id, Text1__c, Text2__c FROM MyCustomObj__c WHERE Text1__c IN ('a', 'b', 'c') AND Text2__c IN ('1', '2', '3')

It will return all records like:

Id  Text1    Text2
1   a        2
2   b        2
3   c        1
4   a        1

However, what I really want is

Id  Text1    Text2
2   b        2
4   a        1

I want to ask that, is there any way I can do such same SQL logic with SOQL? I have found TempTableContract: https://help.salesforce.com/s/articleView?id=sf.modeler_vscode_temp_table_contract.htm&type=5 however, it doesn't seem to build dynamically. In my use case, the temp table need to be built at run time.

Answer 1

Unfortunately, the best you can do is link together AND and OR filters, as in:

SELECT
  Text1__c, Text2__c 
FROM
  MyCustomObj__c
WHERE
  (Text1__c = 'a' AND Text2__c = '1') OR
  (Text1__c = 'b' AND Text2__c = '2') OR
  (Text1__c = 'c' AND Text2__c = '3')

This is pretty easy to do in Apex, but has to be done in code. You may need to run multiple queries if these conditions grow too large to handle in a single query.