Skip to main content

SubQuery

Construct sub-query with provided API.

SOQL.of(Account.SObjectType)
    .with(SOQL.SubQuery.of('Contacts')
        .with(Contact.Id, Contact.Name, Contact.Phone)
    )
    .toList();

Methods​

The following are methods for SubQuery.

INIT

SELECT

SUBQUERY

WHERE

ORDER BY

LIMIT

OFFSET

FOR

INIT​

of​

Constructs a SubQuery.

Signature

SubQuery of(String ofObject)

Example

SELECT Id, (
    SELECT Id
    FROM Contacts
) FROM Account
SOQL.of(Account.SObjectType)
    .with(SOQL.SubQuery.of('Contacts'))
    .toList();

SELECT​

with field1 - field5​

Signature

SubQuery with(SObjectField field);
SubQuery with(SObjectField field1, SObjectField field2);
SubQuery with(SObjectField field1, SObjectField field2, SObjectField field3);
SubQuery with(SObjectField field1, SObjectField field2, SObjectField field3, SObjectField field4);
SubQuery with(SObjectField field1, SObjectField field2, SObjectField field3, SObjectField field4, SObjectField field5);

Example

SELECT Id, (
    SELECT Id, Name
    FROM Contacts
) FROM Account
SOQL.of(Account.SObjectType)
    .with(SOQL.SubQuery.of('Contacts')
        .with(Contact.Id, Contact.Name)
    )
    .toList();

with fields​

Use for more than 5 fields.

Signature

SubQuery with(List<SObjectField> fields)

Example

SELECT Id, (
    SELECT Id, Name, Phone, RecordTypeId, Title, Salutation
    FROM Contacts
) FROM Account
SOQL.of(Account.SObjectType)
    .with(SOQL.SubQuery.of('Contacts')
        .with(new List<SObjectField>{
            Contact.Id,
            Contact.Name,
            Contact.Phone,
            Contact.RecordTypeId,
            Contact.Title,
            Contact.Salutation
        })
    )
    .toList();

with related field1 - field5​

Allows to add parent field to a query.

Signature

Single Related Field Method
SubQuery with(String relationshipName, SObjectField field)
Two Related Fields Method
SubQuery with(String relationshipName, SObjectField field1, SObjectField field2);
Three Related Fields Method
SubQuery with(String relationshipName, SObjectField field1, SObjectField field2, SObjectField field3);
Four Related Fields Method
SubQuery with(String relationshipName, SObjectField field1, SObjectField field2, SObjectField field3, SObjectField field4);
Five Related Fields Method
SubQuery with(String relationshipName, SObjectField field1, SObjectField field2, SObjectField field3, SObjectField field4, SObjectField field5);

Example

SELECT Id, (
    SELECT CreatedBy.Id, CreatedBy.Name
    FROM Contacts
) FROM Account
SOQL Lib Related Fields
SOQL.of(Account.SObjectType)
    .with(SOQL.SubQuery.of('Contacts')
        .with('CreatedBy', User.Id, User.Name)
    )
    .toList();

Signature

SubQuery with(String relationshipName, Iterable<SObjectField> fields)

Example

SELECT Id, (
    SELECT CreatedBy.Id, CreatedBy.Name
    FROM Contacts
) FROM Account
SOQL.of(Account.SObjectType)
    .with(SOQL.SubQuery.of('Contacts')
        .with('CreatedBy', new List<SObjectField>{
            User.Id, User.Name
        })
    )
    .toList();

with string fields​

Signature

SubQuery with(String fields)

Example

SELECT Id, (
    SELECT Id, Name, Phone, RecordTypeId, Title, Salutation
    FROM Contacts
) FROM Account
SOQL.of(Account.SObjectType)
    .with(SOQL.SubQuery.of('Contacts')
        .with('Id, Name, Phone, RecordTypeId, Title, Salutation')
    )
    .toList();

SUB-QUERY​

with subquery​

Query Five Levels of Parent-to-Child Relationships in SOQL Queries

Use SOQL to query several relationship types.

Signature

SubQuery with(SOQL.SubQuery subQuery)

Example

SELECT Name, (
    SELECT LastName , (
        SELECT AssetLevel FROM Assets
    ) FROM Contacts
) FROM Account
SOQL.of(Account.SObjectType)
    .with(SOQL.SubQuery.of('Contacts')
        .with(Contact.LastName)
        .with(SOQL.SubQuery.of('Assets')
            .with(Asset.AssetLevel)
        )
    ).toList();

WHERE​

whereAre​

For more details check SOQL.FilterGroup and SOQL.Filter

Signature

SubQuery whereAre(FilterClause conditions)

Example

SELECT Id, (
    SELECT Id
    FROM Contacts
    WHERE Id = :contactId OR Name = '%John%'
) FROM Account
SOQL.of(Account.SObjectType)
    .with(SOQL.SubQuery.of('Contacts')
        .whereAre(SOQL.FilterGroup
            .add(SOQL.Filter.with(Contact.Id).equal(contactId))
            .add(SOQL.Filter.with(Contact.Name).contains('John'))
            .conditionLogic('1 OR 2')
        )
    )
    .toList();

ORDER BY​

order by​

Signature

SubQuery orderBy(SObjectField field)

Example

SELECT Id, (
    SELECT Id
    FROM Contacts
    ORDER BY Name
) FROM Account
SOQL.of(Account.SObjectType)
    .with(SOQL.SubQuery.of('Contacts')
        .orderBy(Contact.Name)
    )
    .toList();

orderBy string field​

Signature

SubQuery orderBy(String field)

Example

SELECT Id, (
    SELECT Id
    FROM Contacts
    ORDER BY Name
) FROM Account
SOQL.of(Account.SObjectType)
    .with(SOQL.SubQuery.of('Contacts')
        .orderBy('Name')
    )
    .toList();

Order SOQL query by parent field.

Signature

SubQuery orderBy(String relationshipName, SObjectField field)

Example

SELECT Id, (
    SELECT Id
    FROM Contacts
    ORDER BY CreatedBy.Name
) FROM Account
SOQL.of(Account.SObjectType)
    .with(SOQL.SubQuery.of('Contacts')
        .orderBy('CreatedBy', User.Name)
    )
    .toList();

sortDesc​

Default order is ascending (ASC).

Signature

SubQuery sortDesc()

Example

SELECT Id, (
    SELECT Id
    FROM Contacts
    ORDER BY Name DESC
) FROM Account
SOQL.of(Account.SObjectType)
    .with(SOQL.SubQuery.of('Contacts')
        .orderBy(Contact.Name)
        .sortDesc()
    )
    .toList();

sort​

Signature

SubQuery sort(String direction)

Example

SELECT Id, (
    SELECT Id
    FROM Contacts
    ORDER BY Name DESC
) FROM Account
SOQL.of(Account.SObjectType)
    .with(SOQL.SubQuery.of('Contacts')
        .orderBy('Name')
        .sort('DESC')
    )
    .toList();

nullsLast​

By default, null values are sorted first (NULLS FIRST).

Signature

SubQuery nullsLast()

Example

SELECT Id, (
    SELECT Id
    FROM Contacts
    ORDER BY Name NULLS LAST
) FROM Account
SOQL.of(Account.SObjectType)
    .with(SOQL.SubQuery.of('Contacts')
        .orderBy(Contact.Name)
        .nullsLast()
    )
    .toList();

LIMIT​

setLimit​

Signature

SubQuery setLimit(Integer amount)

Example

SELECT Id, (
    SELECT Id
    FROM Contacts
    LIMIT 100
) FROM Account
SOQL.of(Account.SObjectType)
    .with(SOQL.SubQuery.of('Contacts')
        .setLimit(100)
    )
    .toList();

OFFSET​

offset​

Signature

SubQuery offset(Integer startingRow)

Example

SELECT Id, (
    SELECT Id
    FROM Contacts
    OFFSET 10
) FROM Account
SOQL.of(Account.SObjectType)
    .with(SOQL.SubQuery.of('Contacts')
        .offset(10)
    )
    .toList();

FOR​

forReference​

Signature

SubQuery forReference()

Example

SELECT Id, (
    SELECT Id
    FROM Contacts
    FOR REFERENCE
) FROM Account
SOQL.of(Account.SObjectType)
    .with(SOQL.SubQuery.of('Contacts')
        .forReference()
    )
    .toList();

forView​

Signature

SubQuery forView()

Example

SELECT Id, (
    SELECT Id
    FROM Contacts
    FOR VIEW
) FROM Account
SOQL.of(Account.SObjectType)
    .with(SOQL.SubQuery.of('Contacts')
        .forView()
    )
    .toList();