Filter Results

Filter objects are used to filter the result set of records. All sObjects in the schema have a corresponding Filter type created, which combines the object API name with the _Filter suffix. For example, Account has a corresponding Account_Filter type created.

Each field on the sObject has a corresponding input object field on the Filter type. The input object field has an Operators type. For example, Account has a field Name of type String, and so the AccountFilter input object has an input object field Name of type StringOperators.

Each Operators type exposes the filtering capabilities of that field type. Each field type that supports filtering has a corresponding Operators type. The Operators type has one input object field for each filtering capability of the underlying data type. For example, the StringOperators type can compare String values based on equality and ordering.

If multiple operations are specified on an Operator, both operations are included in the query and AND'd together. For example, where: {AnnualRevenue: {gt: 10000, lt: 100000}} finds records with an annual revenue of more than 10000 and less then 100000.

Each field on the Operators type corresponds to the following functions:

Fields on sObjects have corresponding mappings to Operators types. The main field types are:

  • String
  • Double
  • Int
  • Boolean
  • ID
  • Time
  • Picklist
  • Multi-picklist
  • Date and DateTime

String Field Types

Operators for field types Email, TextArea, LongTextArea, Url, and PhoneNumber are similar to StringOperators, but they use the appropriate scalar for the input object field type.

The like operator behaves similarly to the WHERE SOSL expression.

Double Field Types

Operators for field types Currency, Percent, Longitude, and Latitude are similar to DoubleOperators, but they use the appropriate scalar for the input object field type.

Int Field Types

The Operator for the Long field type is similar to IntegerOperators, but uses Long instead of Int.

Boolean Field Types

Boolean fields map to the BooleanOperators filter type.

ID Field Types

ID fields map to the IdOperators filter type.

Time Field Types

Time fields map to the TimeOperators filter type.

Picklist Field Types

Picklist fields map to the PicklistOperators filter type.

Multi-Picklist Field Types

Multi-picklist fields map to the MultiPicklistOperators filter type.

Date and DateTime Field Types

Date and DateTime fields support additional filtering criteria beyond the standard set of operators defined on other types. Rather than the input object fields of the Operators type being the scalar type, they are of input object type DateInput and DateTimeInput respectively.

In addition to a different type for the individual operators, Date and DateTime Operators support additional functions related to the relative time of the field value. These input object field types are DateFunctionInput and DateTimeFunctionInput.

Date fields are mapped to DateOperators.

DateInput facilitates operating on Date fields by more than just the exact value of a particular Date.

Date literals represent a relative range of time, such as last month, this week, or next year. See SOQL and SOSL Reference: Date Formats and Date Literals in WHERE Clauses.

Range values for dates capture dates within a period of time. See SOQL and SOSL Reference: Date Formats and Date Literals in WHERE Clauses.

Date functions help you convert date values to the default time zone. See SOQL and SOSL Reference: Converting Time Zones in Date Functions.

DateTime fields map to the DateTimeOperators filter type.

DateTime fields map to the DateTiimeInput filter type.

DateTime functions help you convert date time values to the default time zone. See SOQL and SOSL Reference: Converting Time Zones in Date Functions.

DateTime primitive operators allow working with DateTime values directly.

Examples

Here are a few sample arguments to demonstrate how to filter on a field:

This example queries opportunities whose NextStep equals "Need estimate".

This example queries accounts whose names match a given string.

This example queries accounts with an annual revenue of more than 1 million.

Some queries want to combine several predicates to further filter the result set. Whenever two Operators have a value on the filter type, they’re combined in the query with an implicit AND operator.

The filter types also have fields for the AND, OR, and NOT functions. These field types take in a list of filters, and so when combined allow for arbitrary boolean expressions.

Each Filter within the list of Filters will be joined by the named operator. That is, a filter that looks like this:

The previous filter is translated to a SOQL statement that looks like this:

Similarly, an OR function looks like this:

The OR function is translated to the SOQL statement:

Futhermore, we can have arbitrary nesting of filters.

The nesting of filter is translated as follows.

The NOT function inverts the predicate within the filter.

The NOT function can be used with the binary functions as well.

The previous NOT function is translated to:

So far, we’ve discussed how fields are mapped into the Filter type and how to combine filters with the AND, OR, and NOT functions. In addition, each parent relationship the sObject is added to the Filter type as well. If the parent relationship is non-polymorphic, then the corresponding Filter type for that object is used.

If the parent relationship is polymorphic, then an input object following the naming convention <ObjectName>_<RelationshipName>_Filters is constructed and used as the Filter type for that parent relationship. For example, the SocialPost object's Who filter has a type of SocialPost_Who_Filters. This input object has one input object field for each concrete type that participates in the polymorphic relationship.

With this structure, it's possible to filter the result set by the value of the underlying polymorphic types data. Each Filter is combined with an AND condition to assert the polymorphic relationship is of the filter type.