8

Related: OR/AND condition for complex criteria with remote objects

Background

The retrieve query format allows the use of or and and clauses to create complex queries. The relevant documentation is at the end of this post.

But, JS maps only allows one value per key.

Question

How do I specify an or of one field's value? For instance, how do I build a query for an account where (BillingState is any of (Hawaii, Alaska, or California)) and (Name is like % Assoc%)?

The obvious answer (below) does not work, because JS engines will simply use the last value for BillingState ({ eq: 'California'}):

where: {
  or: {
    BillingState: { eq: 'Hawaii' },
    BillingState: { eq: 'Alaska' },
    BillingState: { eq: 'California' }
  }, 
  Name: { like: '% Assoc%' }
}

Official Documentation

Also see salesforce docs.

where conditions enable you to filter the results of a retrieve operation, much the same way that a WHERE condition in a SOQL query does. The operators that are available for where conditions are:

eq: equals
ne: not equals
lt: less than
lte: less than or equals
gt: greater than
gte: greater than or equals
like: string matching. As with SOQL, use “%” as a wildcard character.
and: logical AND, used for combining conditions
or—logical OR, used for combining conditions

Within the where object, add field name and condition pairs to create complex criteria. Multiple conditions by default are treated as AND conditions. You can use and and or to create other criteria conditions. For example:

{
  where: {
    or: {
      FirstName: { like: "M%" },
      Phone: { like: '(415)%' }
    }
  }
}
  • This very basic use case (as it stands) is no longer a problem with the quiet release of in and nin in the API. However, there may still be more complex cases that require the hack below. – Charles Koppelman Nov 30 '15 at 16:06
  • An example of a more complex case still not handled well is or: { Name: { like: x }, Name: { like: y } } – Charles Koppelman Nov 30 '15 at 19:40
6

OPTION 1: Nested logical constructions to prevent the clash of identifiers

where: {
  or: {
    BillingState: { eq: 'Hawaii' },
    or: {
      BillingState: { eq: 'Alaska' },
      or: {...},
    },    
  }, 
  Name: { like: '% Assoc%' }
}

Caution: it seems to be important, that all ors and ands have at least two nodes. So if necessary, you have to add fake-conditions which evaluate neutrally to your needs.


OPTION 2: Add the Model fields twice (not verified)

<apex:remoteObjects jsNamespace="RemoteObjectModel">
   <apex:remoteObjectModel name="Account" >
      <apex:remoteObjectField name="BillingState" jsShorthand="State1"/>
      <apex:remoteObjectField name="BillingState" jsShorthand="State2"/>
   </apex:remoteObjectModel>
</apex:remoteObjects>
|improve this answer|||||
  • brilliant (and really annoying)! Let me see if that works! – Charles Koppelman Jul 25 '14 at 20:45
  • Nope - Invalid criteria specified for retrieval. ValidationError [code=11, message=Data does not match any schemas from &quot;oneOf&quot;, path=/where, schemaKey=null] – Charles Koppelman Jul 25 '14 at 20:48
  • It looks like you can't nest an or in an or – Charles Koppelman Jul 25 '14 at 20:52
  • no, looks like it gets converted pretty early on in the process – Charles Koppelman Jul 25 '14 at 20:56
  • 1
    The important bit is that all ors and ands need at least two nodes. To fill in your ..., I did: and: { Name: {like: '%'}, BillingState: {eq: 'California'} } – Charles Koppelman Jul 25 '14 at 21:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.