I have a many to many relationship with 3 objects Position, Candidate, Job Application(Junction Object). I wanted to retrieve all the positions applied by a particular candidate and also the positions not applied by any candidate using one query. Is it possible using semi joins and anti joins? Or I have to create separate lists and then combine them into third list.
2 Answers
You need 2 Queries because you cannot query using the "OR" clause in a subquery. In SOQL it is either include specific child records or exclude child records but not both. I have used sub queries in the select clause and then used when in other records as a subquery but it would not work in this situation because you want to exclude records that you need.
I do want to share this blog. I think it gives a great explanation of what can be done with SOQL.
https://blog.jeffdouglas.com/2010/02/22/soql-how-i-query-with-thee-let-me-count-the-ways/
-
2
-
I don't have a schema as such. The original objects are the different ones. Position__c ---<---- Job_Application__c --->--- Candidate__c– SaloniCommented Mar 22, 2019 at 18:53
-
I edited my previous answer. There are ways to get around the no 'or' clause in subqueries but it won't work in your use case. Soql won't let you use exclude and include filter results when using a subquery. Commented Mar 23, 2019 at 22:13
-
Thats what I thought to create 2 queries and combine the result in one. But thanks for the explanation. Really appreciate it.– SaloniCommented Mar 25, 2019 at 12:55
Yes it should be possible. I believe you are limited to two semi- or anti-joins in a single query, but you only need two. Making some assumptions about API Names, a query looking something like the below should work:
SELECT Id FROM Position__c WHERE Id IN (
SELECT Position__c FROM Job_Application__c
WHERE Candidate__c = 'xyz'
) OR Id NOT IN (
SELECT Position__c FROM Job_Application__c
)
You might also be limited to one join on a specific table in one query, I'm not sure but will investigate and update this answer.
answered Mar 22, 2019 at 18:33
-
Thanks Adrian. I also tried to do this, but with Salesforce there is a limitation that we cannot use subqueries with OR.– SaloniCommented Mar 22, 2019 at 18:42
-
Then it is not possible and you need two separate queries, one for each clause.– Adrian Larson ♦Commented Mar 22, 2019 at 18:43
-
@AdrianLarson I suggest you update this answer given the OR issue raised by Saloni (and just hit by me in my package development work!)– Phil WCommented Jun 16, 2020 at 15:24