4

There are certain fields in certain sObjects, which can't be used in WHERE clauses in SOQL queries. One example is:

"SELECT Id FROM Vote WHERE SystemModstamp > 2014-11-19T14:32:14.802+01:00"

You get an error like:

[ApiFault  exceptionCode='MALFORMED_QUERY'
exceptionMessage='Implementation restriction: When querying the Vote object, 
you must filter using the following syntax: 
ParentId = [single ID], Parent.Type = [single Type], Id = [single ID], or Id IN [list 
of ID's].'
]

Right now, I'm having this problem with two sObjects, IdeaComment and Vote. However, my queries are dynamically generated, and the type of the object to be queried isn't know in advance. Is there any API-tastic way of finding out which fields of certain sObjects can't be used in WHERE clauses? E.g. given a list of sObject types, for each one of them, I need to know which fields I can and which I can't use in WHERE clauses.

I've had a look at the docs, the only relevant part I could find was this, however it only added to the confusion. E.g. it says:

Filter  Can be used as filter criteria in a SOQL query FROM or WHERE clause.

However, SystemModstamp is filterable but still can't be used in WHERE clause.

UPDATE-1

I can live with using SystemModstamp, and that's not a problem. However, I need to know when can and when can't I use a certain fields in a WHERE clause.

  • I think what the error message says it's not that you can't use SystemModstamp, but that you have to query including at least one of the listed filters. – MLucci Nov 19 '14 at 13:59
  • @MLucci I can live with that. However, I need to know, not only in the case of a Vote object, but in the case of all other objects, if I have to do the same. – Haris Osmanagić Nov 19 '14 at 14:05
  • Funny thing is that from the Workbench I can't see any Parent field on the Vote object, even though is described as Vote standard field - but probably just because I don't have Ideas activated in my ORG. Anyway, this looks as a veeeery particular case: your question is still worth to be answered - "how can I get query restrictions that are enforced on sObjects?" - but probably for the Vote object the answer is not obvious/not easy to find anyway. – MLucci Nov 19 '14 at 14:18
  • For such cases I do the following: do any kind of query which is possible, than filter by date in-memory, than retrieve all records using the filtered IDs.;) – Haris Osmanagić Nov 19 '14 at 14:39
2

The Schema methods does not help here. This is a complex extraordinary restriction applying only for Vote: http://www.salesforce.com/developer/docs/api/Content/sforce_api_objects_vote.htm

The restriction is to filter at least one of fields. Most likely evaluated late (on runtime) by salesforce.

A SOQL query must filter using one of the following Parent or Id clauses.

ParentId = [single ID]

Parent.Type = [single type]

Id = [single ID]

Id IN = [list of IDs]

So best thing I can figure right now is to catch the exception an parse the error message string.

| improve this answer | |
  • Thanks for the reply Uwe! This restriction applies also to IdeaComments, so I guess there may be other similar restrictions as well. – Haris Osmanagić Nov 19 '14 at 15:53
  • Yeah, that's possible and the entire "Ideas" entities seem to be a bit special. Amongst standard objects you may encounter such special behaviors - but they tend to be rare. Fetching those programmatically is probably not possible. So a kind of workaround could be the best you'll get. – Uwe Heim Nov 19 '14 at 15:59
  • Yep, and I'm still getting used to those special behavior situations.:) The only workaround now is just using the software, and waiting for a MALFORMED_QUERY error to appear.:) – Haris Osmanagić Nov 19 '14 at 16:05

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.