Understanding Relationships in Salesforce and working with SOQL in Laravel Eloquent

In Salesforce Object Query Language (SOQL), joins are handled differently compared to traditional SQL. SOQL does not support SQL-like joins directly; instead, it uses a concept known as relationship queries.

These relationship queries can be classified into two main types:

1) Child-to-parent (downward) queries:

These type are queries are used to retrieve data from a child object while accessing related fields from a parent object. This is similar to performing an inner join in SQL where data is retrieved from a child table and joined with a parent table based on a foreign key.

2) Parent-to-child (upward) queries:

These queries retrieve data from a parent object while including related records from a child object. This is similar to a SQL subquery where child records are grouped under the parent record.

Suppose, Account is a parent of Contact.

Downward queries use dot notation to navigate from the child object to the parent object.

 SELECT FirstName, Account.Name from Contact

Here you have to use the name of the relationship (Account) not the name of the field (Account).

Upward queries use subqueries to retrieve child records

SELECT Name, (SELECT Contact.FirstName, Contact.LastName FROM Contacts) FROM Account

Here Contact will be relationship name between the objects, not the object name. Sometimes child relationship on the parent object, typically ending with ‘__r’, if it’s a custom relationship.

A point to note is that sorting child records in an upward SOQL query isn’t directly possible using SOQL itself. In Salesforce, when you perform a parent-to-child subquery, the child records are fetched as a related list without the ability to sort them directly in the query. However, once you have retrieved the data, you can perform additional sorting operations on the parent fields in your application code, which could be inefficient.

Connecting Laravel Eloquent with Salesforce:

You can use this package to abstract the connection with Salesforce objects, so you don’t need to write SOQL queries manually:
https://roblesterjr04.github.io/EloquentSalesForce/index.html#/

Most of the setup is covered in their documentation itself. You can check it out. I am just mentioning the uncovered ones, which is handling the above-discussed relationships with this package.

For normal Salesforce objects, which are already available in Salesforce, you can use hasMany and belongsTo methods as discussed in their documentation.

But, when handling custom objects and relationships, you cannot use those methods. You have to either use subqueries or dot notation mentioned above.

For example, child-to-parent and parent-to-child relationships go like this:

Child-to-Parent:

<?php

namespace App\SalesforceModels;

use Lester\EloquentSalesForce\Model;
use Lester\EloquentSalesForce\Database\SOQLBuilder;

class Company extends Model
{
    protected $table = 'Company__c';

    public $columns = [
        'Id',
        'Name',
        '(SELECT Id, Name FROM Shareholders__r)',
    ];
}

Parent-to-Child:

<?php

namespace App\SalesforceModels;

use Lester\EloquentSalesForce\Model;
use Lester\EloquentSalesForce\Database\SOQLBuilder;

class Shareholders extends Model
{
    protected $table = 'Shareholders__c';

    public $columns = [
        'Id',
        'Name',
        'Company__c',
        'Company__r.Id',
        'Company__r.Name'
    ];
}

A point to note here is that Company__c and Shareholder__c are custom Salesforce objects as indicated by the __c notation, and there exists a one-to-many relationship between Company and Shareholders with the relationship name Shareholders__r and a many-to-one relationship between Shareholders and Company with the relationship name Company__r, which are also custom relationships.

You may try to use abstract joins provided by the package like this:

Company::join('Shareholders__c', 'Id', '=', 'Company__c')->select(['Id', 'Name', 'Shareholders__c.Id', 'Shareholders__c.Name']);

Which is actually converted into:

SELECT Id, Name, (SELECT Id, Name FROM Shareholders__c) FROM Company__c

But it should be Shareholders__r as we discussed above.

Or in a child-to-parent form:

Shareholders::join('Company__c', 'Company__c', '=', 'Company__c.Id')->select(['Id', 'Name', 'Company__c.Id', 'Company__c.Name']);

Goes like this:

SELECT Id, Name, Company__c.Id, Company__c.Name FROM Shareholders__c

Here also it should be Company__r.Id, Company__r.Name.

So you may also try to do like this:

Company::join('Shareholders__r', 'Id', '=', 'Company__c')->select(['Id', 'Name', 'Shareholders__r.Id', 'Shareholders__r.Name']);

Replacing the actual table name with the relationship name, but if you do that, the package’s underlying process is that it runs a describe query to the table before running the actual query, so you will encounter ‘Shareholder__r’ resource not found, since it is not actually a table name.

SO YOU CANNOT USE JOINS EVEN USING THIS PACKAGE WHILE WORKING WITH CUSTOM OBJECTS AND RELATIONSHIPS.

So to bypass all these problems, you can simply set up the model like above and use select queries. It automatically fetches the related object. But the only thing is it fetches in the form like this:

{
    "attributes": {
        "type": "Company__c",
        "url": "/services/data/v61.0/sobjects/Company__c/001Q100000ArOVGIA3"
    },
    "Id": "001Q100000ArOVGIA3",
    "Name": "Apple Inc.",
    "Shareholders__r": {
        "totalSize": 2,
        "done": true,
        "records": [
            {
                "attributes": {
                    "type": "Shareholders__c",
                    "url": "/services/data/v61.0/sobjects/Shareholders__c/a4aQ1000000EltVIBS"
                },
                "Id": "a4aQ1000000EltVIBS",
                "Name": "Venture Finance"
            },
            {
                "attributes": {
                    "type": "Shareholders__c",
                    "url": "/services/data/v61.0/sobjects/Shareholders__c/a2aA1000000fHrtVIAE"
                },
                "Id": "a2aA1000000fHrtVIAE",
                "Name": "Inuvest Tech"
            }
        ]
    }
}

So you may have to manually handle it. Maybe you add this method in the model:

public function getShareHolders()
{
    return $this->Shareholders__r['records'] ?? [];
}

I hope this article might be useful for you. Thank you for reading!

References:
[1] https://github.com/roblesterjr04/EloquentSalesForce/issues/34

[2] https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_understanding.htm