2

Due to the Data storage limits in our Salesforce Instance, we have created a big object with 3 Indexed fields. Now, when I use this Big object in an apex class to search for a particular value from the Big object records, I had to include all 3 indexed fields in the WHERE Clause due to Big Object SOQL Limitation. As per the existing Apex class logic, we should be able to search even with one parameter. Please help me to find a way to bypass Big object SOQL Limitation without changing the current Apex logic?

If I execute the below Apex class as is, I am getting an error: "Filters may not have any gaps within the composite key" in the Workbench Rest API explorer.

Apex code: *

@RestResource(urlMapping='/locationservice')
global class LocationService 
{
    @HttpGet
    global static void doGet() 
    {
        //variable to collect the params from apexrest url
        string Zcode = RestContext.request.params.get('Zipcode');
        string ste  = RestContext.request.params.get('State');
        string city = RestContext.request.params.get('City');
        string mlrange = RestContext.request.params.get('milerange');
        string lat;
        string lon;
{
if(ste!=null)
    {
list<GeoCoordinate__b> lstPostalCodeMDT = [select Latitude__c, Longitude__c from GeoCoordinate__b where State__c= :ste];//soql to query Geocoordinate Big Object
        if(!lstPostalCodeMDT.isempty())
        {
           lat = lstPostalCodeMDT.get(0).Latitude__c;
           lon = lstPostalCodeMDT.get(0).Longitude__c;
        }
    }
if(city!=null)
    {
list<GeoCoordinate__b> lstPostalCodeMDT = [select Latitude__c, Longitude__c from GeoCoordinate__b where City__c = :city];//soql to query Geocoordinate Big Object
        if(!lstPostalCodeMDT.isempty())
        {
           lat =  lstPostalCodeMDT.get(0).Latitude__c;
           lon = lstPostalCodeMDT.get(0).Longitude__c;
        }
    }
if(Zcode!=null)
    {
        list<GeoCoordinate__b> lstPostalCodeMDT = [select Latitude__c, Longitude__c from GeoCoordinate__b where Zip_code__c = :Zcode];//soql to query Geocoordinate Big Object
        if(!lstPostalCodeMDT.isempty())
        {
           lat = lstPostalCodeMDT.get(0).Latitude__c;
           lon = lstPostalCodeMDT.get(0).Longitude__c;
        }
    }
}
decimal lt = decimal.valueOf(lat);
decimal lg = decimal.valueOf(lon);
decimal mlran = decimal.valueOf(mlrange);

//SOQL to fetch the dealer data from Account object
list<account> locationList = [SELECT Id, Name, Phone, ShippingStreet, ShippingCity, ShippingState, ShippingCountry, ShippingLatitude, ShippingLongitude, ShippingPostalCode, Website, distance(ShippingAddress, geolocation (:lt, :lg), 'mi') distance FROM Account WHERE distance(ShippingAddress, geolocation (:lt, :lg),'mi') < :mlran ORDER BY distance(ShippingAddress, geolocation (:lt, :lg), 'mi') LIMIT 20];

//pass the results in JSON format     
String locationJSON = JSON.serialize(locationList);
RestContext.response.addHeader('Content-Type', 'application/json');
RestContext.response.responseBody = Blob.valueOf(locationJSON);    
    }
}

*

1 Answer 1

7

That's simply not how SOQL on BigObjects works. There isn't an end-run around fulfilling the requirements to query based on your BigObject's defined index fields:

Build an index query starting from the first field defined in the index, without gaps between the first and last field in the query. You can use =, <, >, <=, or >=, or IN on the last field in your query. Any prior fields in your query can only use the = operator

(Emphasis mine).

You can omit fields from the composite primary key only from the right. So, if your index is composed of fields A__c, B__c, and C__c, in that order, you can query on, as illustrated with examples in the linked documentation,

  • A__c, B__c, and C__c
  • A__c, and B__c
  • A__c

You cannot query on

  • B__c and C__c
  • C__c
  • A__c and C__c

To do so, you would need to switch to Async SOQL, with everything that entails.

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .