Skip to main content

SubQuery

Construct sub-query with provided API.

Methods

The following are methods for SubQuery.

INIT

SELECT

SUBQUERY

WHERE

ORDER BY

LIMIT

OFFSET

FOR

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

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

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