SubQuery

Construct sub-query with provided API.

public interface SubQuery { // SOQL.SubQuery
    SubQuery of(String ofObject);

    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);
    SubQuery with(List<SObjectField> fields); // For more than 5 fields
    SubQuery with(String relationshipName, List<SObjectField> fields);
    SubQuery with(SubQuery subQuery); // SOQL.SubQuery

    SubQuery whereAre(FilterGroup filterGroup); // SOQL.FilterGroup
    SubQuery whereAre(Filter filter); // SOQL.Filter

    SubQuery orderBy(SObjectField field);
    SubQuery orderBy(String relationshipName, SObjectField field);
    SubQuery sortDesc();
    SubQuery nullsLast();

    SubQuery setLimit(Integer amount);

    SubQuery offset(Integer startingRow);

    SubQuery forReference();
    SubQuery forView();
}

of

Conctructs an 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 fields

Signature

SubQuery with(String relationshipName, List<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 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();

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

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 related

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();

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();

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

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();