1

I currently have a page where I query a custom object (Projects) to filter the results using the Distance formula(calculating distance between two addresses). I display these results using a standard set controller in my visual force page. In this case there was only one object called projects. I now have a requirement to query two separate custom objects(Projects) and (new custom object) and return one result set, which in SQL could be accomplished using the UNION operator. But SOQL does not support the UNION operator. What would be a workaround for this? Note: I cannot use SOSL as this is not a text based search

I am thinking of retrieving the results from each object separately first. Create a apex object(CombinedResults) with the fields needed. Then iterate the two result sets to create one list of type CombinedResults. I would not be able to use the standard set Controller in this case. But I can use the apex-lang package which supports pagination of custom apex object.

Has anybody dealt with a similar situation? If so, what was the workaround?

  • Can't a nested SOQL solve this? – EricSSH Aug 29 '16 at 20:28
  • 1
    Seems like a job for a RecordType on Project. – Charles Koppelman Aug 29 '16 at 21:13
  • What is the relationship between Projects and the new custom object? Is that relationship represented in Salesforce? I'm assuming there must be a relationship beyond just both having an address so that it makes sense to display them together. – Daniel Ballinger Aug 29 '16 at 22:40
  • The new custom object is called 'Archived Projects'. Each record of this object will also display a link to our legacy Project Tracking system. This new object will mainly be used to hold older projects between 2003 through 2014. This object will only contain few read only fields. The user would click on the link to the legacy system to view more data. the current 'Projects' object will have editable fields and no link to the legacy system. Is it possible to implement the 'Archived Projects' as a record type? – Neema Aug 30 '16 at 13:31
1

Pretty sure you need two queries and a Comparable implementation. Luckily for you DISTANCE is supported in SELECT clauses.

The sorting wrapper can be fairly simple:

public class DistanceSorter implements Comparable
{
    final SObject record;
    final Decimal distance;
    public DistanceSorter(SObject record, Decimal distance)
    {
        this.record = record;
        this.distance = distance;
    }
    public Integer evaluate(Object instance)
    {
        DistanceSorter that = (DistanceSorter)instance;
        return (this.distance - that.distance).round(RoundingMode.UP);
        // the above implementation implementation sorts closest first
        // to reverse, just swap this and that
    }
}

Then you can do the requisite two queries:

List<DistanceSorter> wrappers = new List<DistanceSorter>();
for (Project__c record : [SELECT DISTANCE(...) distance FROM Project__c WHERE ...])
{
    wrappers.add(new DistanceSorter(record, (Decimal)record.get('distance'));
}
for (OtherObject__c record : [SELECT DISTANCE(...) distance FROM OtherObject__c WHERE ...])
{
    wrappers.add(new DistanceSorter(record, (Decimal)record.get('distance'));
}
wrappers.sort();

List<SObject> combinedRecords = new List<SObject>();
for (DistanceSorter wrapper : wrappers)
{
    combinedRecords.add(wrapper.record);
}
  • Thank You Adrian. I am not sure if Distance is supported in the select clause. I tried the following query in the Query Editor, all the values except the distance is displayed. Select DISTANCE( Decimal_LatLon__c,GEOLOCATION( 42.4986, -71.2235) , 'mi'),Decimal_LatLon__latitude__s,Decimal_LatLon__longitude__s,Id, Site_Name__c from pse__proj__c where DISTANCE( Decimal_LatLon__c,GEOLOCATION( 42.4986, -71.2235) , 'mi') < 1.0 – Neema Aug 30 '16 at 14:28
  • @Neema Maybe not in the query editor (though I'm not sure)...but that's not what you asked about. It will work in Apex. – Adrian Larson Aug 30 '16 at 14:59
  • 1
    All I care is that it works in Apex. I always run my query in the Query Editor just to make sure, before I use it in Apex. Thank You again. I will try in Apex. – Neema Aug 30 '16 at 15:26
  • @Neema That is a very good practice. I don't mean to dissuade you from it. Let me know if you have issues implementing the above. Good luck! – Adrian Larson Aug 30 '16 at 15:34

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.