-1

I want to query two unrelated objects, and I can't create a lookup relationship between them.

They have the same global picklist sets in both of them and I want to query the records from one of the objects when the picklists have the same values but the objects are unrelated.

Can anyone help?

3
  • 2
    Welcome to Salesforce Stack Exchange. You picked three tags, but Apex is not one of them. Are you wanting to do this in Apex, just as a manual query in the Developer Console, or from some other direction?
    – Moonpie
    Jun 15, 2021 at 17:23
  • 1
    I've written an answer based on what I think you're asking, but in general you'll find yourself getting better answers if you provide more details about the problem you're trying to solve. The other point is that you should focus on the problem you're trying to solve (e.g. Based on user input from a Lightning Web Component, I want to find and display the Accounts that match data X) rather than the solution you've chosen (e.g. how do I query objects with no relationship between them?)
    – Derek F
    Jun 15, 2021 at 18:11
  • It is normal to worry about bulk querying in the Salesforce context, which is why I have referenced the Q&A above.
    – Phil W
    Jun 15, 2021 at 18:29

1 Answer 1

2

With SOQL alone, it is not possible to query two unrelated objects in a single query. Relationship fields (lookup, master-detail) are the interface we're given to access data on other records from SOQL.

If you can't add a relationship field, then you'd need to need to handle this using a tool besides SOQL. That could be Apex or, if you're using LWC/Aura, Javascript.

The thing to realize here is that you can mimic a relationship using code as long as you have 1 piece of information that is shared between 2 different SObjects (or really, just 2 different records).

In Apex, things like this are what the Map collection type excels at. For SObjects, we usually build a Map<Id, SObject>, but there's nothing stopping you from building a Map<String, SObject>.

For my first example, let's look at a more "normal" map, and how you can use it.

Map<Id, Account> accountMap = new Map<Id, Account>();

// Basic SOQL-for-loop to get account information
for(Account a :[SELECT Id, BillingState, Name FROM Account LIMIT 50]){
    accountMap.put(a.Id, a);
}

// Yes, you could get account information using SOQL in this example, but bear with me.
// Making this somewhat contrived example should make it easier to understand the next
//   example
for(Contact c :[SELECT Id, Name, AccountId FROM Contact LIMIT 50]){
    // I'd normally name this something like 'related' or 'relAcct'
    // Again, just go with the flow for now.
    // We can get our 'adjacent' record by using the common piece of information
    //   on both objects to fetch a value from our map
    Account adjacentAccount = accountMap.get(c.AccountId);

    // If you were to run this example code in your org, it wouldn't be guaranteed
    //   that your Contact would be related to one of the Accounts that we queried,
    //   but let's assume that it is.
    system.debug(adjacentAccount.Name); // would print the account name
}

The important part there is that we were able to get at the information on an Account without explicitly using any relationship data in either of the queries (i.e. no c.account.Name). The only thing that really matters is that both the Account and Contact have a common piece of information (which in this case is the Account Id).

You can apply that idea, using maps to store (and later find) data besides just Ids. Say you wanted to find an Account based on the BillingState

Map<String, Account> accountMap = new Map<String, Account>();

// Basic SOQL-for-loop to get account information
// Assuming that there's only 1 account per BillingState
// Unrealistic, but it keeps the example simple
// You could just as easily build a Map<String, List<Account>> instead if a single
//   value can be used by multiple records
for(Account a :[SELECT Id, BillingState, Name FROM Account LIMIT 50]){
    accountMap.put(a.BillingState, a);
}


for(Contact c :[SELECT Id, Name, AccountId, MailingState FROM Contact LIMIT 50]){
    // MailingState and BillingState are different field names, but the key is that
    //   they can be the same on both Contact and Account
    Account adjacentAccount = accountMap.get(c.MailingState);

    // If you were to run this example code in your org, it wouldn't be guaranteed
    //   that your Contact would be related to one of the Accounts that we queried,
    //   but let's assume that it is.
    system.debug(adjacentAccount.Name); // would print the account name
}

The point I'm making is that having relationships is handy and makes things easier, but in the end it's just a piece of data common to two (or more) records. As long as you have common data, you should be able to construct some code to "relate" the two. Things get more complicated if you have a many-to-many relationship (e.g. You have multiple accounts in a given state, and have multiple contacts in the same state, and would want to display Contacts under an Account but not use the Account Id), but that's where either choosing an appropriate data structure (like, say, a Map<String, List<Account>>) or choosing a better field (or combination of fields) to use to figuratively tie two records together comes into play.

Not the answer you're looking for? Browse other questions tagged or ask your own question.