SubQuery

Construct sub-query with provided API.

Methods

The following are methods for SubQuery.

INIT

• of(String ofObject)

SELECT

• with(SObjectField field)
• with(SObjectField field1, SObjectField field2)
• with(SObjectField field1, SObjectField field2, SObjectField field3)
• with(SObjectField field1, SObjectField field2, SObjectField field3, SObjectField field4)
• with(SObjectField field1, SObjectField field2, SObjectField field3, SObjectField field4, SObjectField field5)
• with(List<SObjectField> fields)
• with(String relationshipName, List<SObjectField> fields)

SUBQUERY

• with(SubQuery subQuery)

WHERE

• whereAre(FilterGroup filterGroup)
• whereAre(Filter filter)

ORDER BY

• orderBy(SObjectField field)
• orderBy(String relationshipName, SObjectField field)
• sortDesc()
• nullsLast()

LIMIT

• setLimit(Integer amount)

OFFSET

• offset(Integer startingRow)

FOR

• forReference()
• forView()

INIT

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

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

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