+ Start a Discussion
djordandjordan 

Faking server side join and IN operators

I am writing a JavaScript based sControl to extend the functionality of accounts. I use a custom object to relate to accounts by storing the account IDs in a custom object field

I would like to have a query similiar to:

select c.fieldList from customObject c, account a
   where c.parentAccountId = a.id and a.country = someCountry

An alternative would be:

select fieldList from customObject
   where parentAccountId IN (select id from account where country = someCountry)

Since server side joins aren't available I'm faking this as:

String ids[] = select id from account where country = someCountry
select fieldList from customObject where parentAccountId = id[1] OR parentAccountId = id[2] OR ... parentAccountId = id[n]

It is possible to have n ~ 1000.

We considered duplicating the country field value in the custom object, but this value may be changed by users modifying the account record via the UI. Unless there is some way of triggering the update of the account.country field to update the customobject.country, or getting the user to manually update the value via a WIL, we cannot rely on the custom object to hold accurate information. (It may be possible to have a daemon process running around to compare the two values and fix any synchronisation issues, but then we introduce the problem of where to host this daemon: sControls are no longer an option)

 

What is the maximum length for the query string?

Is there a limit on the number of logical operators that can be applied to a query? Will rate limting make this query unworkable?

Is there a better way of doing this?

 

Cheers,

David.

adamgadamg
Hi djordan:
 
I have been using something similar myself recently; while I don't know off hand what the limit on SOQL size is, you should take a look at retrieve, as it should be able to do something similar with fewer restrictions. 
 
Adam
djordandjordan

I'm not sure that I can use retrieve as I don't know the IDs of the custom object records: I'm querying against a field which holds the parent account ID.

As far as I can tell, sforce thinks that this is just a plain String field as you can't tell a custom field that it's really holding a reference to another entity.

 

D.