SOQL

Examples

Basic SOQL

select Field1__c,
    Field2__c,
    Field3__c,
    Object__r.ChildField__c,
    OtherField__c,
    (
        select Field4__c
        from RelatedObject__r
    ), (
        select Field5__c
        from OtherRelatedObject__r
        where Field5__c in ('value1', 'value2')
    )
from MyObject__c
where Field1__c = :myVar and
    Field2__c < 2 and
    (
        DAY_IN_YEAR(Field3__c) = 32 or
        Field3__c = null
    )
order by OtherField__c
limit 10
offset 10

Aggregate SOQL

select COUNT(Id) count,
    MAX(Field1__c) max_field,
    Field3__c
from MyObject__c
where Field2__c < 2
group by Field3__c
order by COUNT(Id)

Guidelines

Usage

In general, SOQL should be declared inline using the square bracket notation. When not possible, dynamic SOQL should conform to the same naming and capitalization requirements

Naming

  • Fields should be alphabetized appear in upper camelcase.
  • Related queries should appear after all member queries, also in alphabetical order.
  • Should a field contain special characters (for example underscore), those fields should in ascii order.

Spacing

  • Each field should be on it's own line.
  • The first field should be on the same line as the select.
  • Subsequent fields should be intented further in.
  • Parentheses should be on their own line
  • Where stanzas that are combined with a logical operator (and / or) should have the operator at the end of the line

Capitalization

  • Action words (select, from, where), logical operators (and, or) should be lowercased
  • Date functions (DAY_IN_YEAR, CALENDAR_YEAR), aggregate functions (MAX, AVG), or literals (TODAY) should be capitalized

Checkstyle Rules

<!-- SOQL actions -->
<module name="RegexpSingleline">
	<property name="format" value=".*\b((?!(?-i)select)(?i)SELECT)\b.*" />
	<property name="message" value="SOQL operator ''select'' should be lowercase" />
</module>

<module name="RegexpSingleline">
	<property name="format" value=".*\b((?!(?-i)from)(?i)FROM)\b.*" />
	<property name="message" value="SOQL operator ''from'' should be lowercase" />
</module>

<module name="RegexpSingleline">
	<property name="format" value=".*\b((?!(?-i)where)(?i)WHERE)\b.*" />
	<property name="message" value="SOQL operator ''where'' should be lowercase" />
</module>

<module name="RegexpSingleline">
	<property name="format" value=".*\b((?!(?-i)limit)(?i)LIMIT)\b.*" />
	<property name="message" value="SOQL operator ''limit'' should be lowercase" />
</module>

<module name="RegexpSingleline">
	<property name="format" value=".*\b((?!(?-i)group by)(?i)GROUP BY)\b.*" />
	<property name="message" value="SOQL operator ''group by'' should be lowercase" />
</module>

<module name="RegexpSingleline">
	<property name="format" value=".*\b((?!(?-i)order by)(?i)ORDER BY)\b.*" />
	<property name="message" value="SOQL operator ''order by'' should be lowercase" />
</module>

<!-- SOQL logical operators -->
<module name="RegexpSingleline">
	<property name="format" value=".*\b((?!(?-i)and)(?i)AND)\b.*" />
	<property name="message" value="SOQL operator ''and'' should be lowercase" />
</module>

<module name="RegexpSingleline">
	<property name="format" value=".*\b((?!(?-i)or)(?i)OR)\b.*" />
	<property name="message" value="SOQL operator ''or'' should be lowercase" />
</module>