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?