Parent and Child Relationship Filters

In Field Operators and Boolean Operators, we discussed how fields are mapped into the filter type and how to combine filters with the AND, OR, and NOT functions. In addition, the sObject is added to the filter type for each parent relationship.

Objects can have a child-to-parent relationship or a parent-to-child relationship. For example, the Contact object has a child-to-parent relationship to the Account object. An Account has parent-to-child relationships to Assets, Cases, and Contacts, among other objects.

Child-to-parent relationships can be many-to-one, just like multiple child contacts can be on a parent account.

The previous query is similar to this SOQL statement.

For some objects that can have a parent relationship with the same object, you can query the parent relationship using the Parent field. For example, an account can have a parent account and a campaign can have a parent campaign.

Accounts without parent account records are also returned. The previous query returns this example response.

Here's a query that returns cases with their parent accounts. Cases without parent accounts are also returned by the query.

You can filter records to return only those accounts without a null Parent Id value.

The previous query is similar to this SOQL statement.

Only accounts with parent account records are returned. The previous query returns this example response.

Parent-to-child relationships are usually one-to-many, just like a parent account can have many child contacts. To include subqueries for standard objects, specify the plural form of the object name, which is the name of the relationship for the object. For example, by selecting from Account, you can query the children records via the Cases, Contacts, and Opportunities fields.

Let’s say you want to query cases on an account.

The previous query is similar to this SOQL statement.

You can also query account names and the last name of contacts on each account.

The previous query is similar to this SOQL statement.

Some objects have children relationships to the same object. For example, use the ChildAccounts field to query children accounts on account records. Accounts without children accounts are also returned.

To query object metadata that includes child relationships, pass in the childRelationships field.

You can also traverse the parent-to-child relationship using a reference to a different object. In this case, the CreatedBy field references the Name field on the User object.

The previous query is similar to this SOQL statement.

A query and its subqueries can include the where argument. The argument can filter on a supported object in the current scope via parent relationships.

For example, you can query accounts in a given industry and the last name of every contact whose created by first name is 'Marc'.

The previous query is similar to this SOQL statement.

With a lookup relationship, you can retrieve field information on a child or parent object. For example, you can query accounts and their associated contact names.

Accounts without child contact records are also returned. The previous query returns this example response.

You can also use the orderBy clause to order your child records in ascending or descending order.

The child records are paginated and include cursor and pagination information. By default, the first 10 children records are returned. To specify the number of children records to return, use the first argument on the child object.

With an orderBy argument, records are returned even if the reference field has a null value.

For example, any case records with a null AccountId field are also returned.

The previous query is similar to this SOQL statement.

With a where argument that uses the or boolean operator, records are returned even if the reference field value in a record is null.

For example, if a contact with a specific LastName has a null AccountId field and another contact with a different last name and specific parent account name, the query returns both contacts.

The previous query is similar to this SOQL statement.

With a where argument that checks for a value in a parent field, the record is returned even if the parent value is null.

The previous query is similar to this SOQL statement.

SOQL and SOSL Reference: Understanding Relationship Fields and Polymorphic Fields

Paginate Results