2

Does a Database.query() result maintain the order of the WHERE clause list I'm searching by?

List<Id> myList = new List<Id>;
myList.add('My Opportunity 1');
myList.add('My Opportunity 2');
myList.add('My Opportunity 3');
String soql = 'SELECT FROM Opportunity WHERE Name IN (\'' + myList.get(0) + '\',\'' + myList.get(1) + '\',\'' + myList.get(2) + '\')';
List<Opportunity> myResult = Database.query(soql);

Is it valid that:

myResult.get(0).Name == 'My Opportunity 1'
myResult.get(1).Name == 'My Opportunity 2'
myResult.get(2).Name == 'My Opportunity 3'

Use case is I'm trying to build a deep cloning tool which accepts any record and clones all children, grand-children, great-grand-children, etc. Part of this method requires me to be able to identify which cloned grand-child is related to which original grand-child (so as to parent to the correct cloned great-grand-children). I would like to compare the input list with the output list and associate (0) with (0) and (1) with (1), but that only works if the Database.query() method maintains the same sort order.

One option is to create a field on all the objects that stores the "cloned from" ID, but this is far from ideal for a truly scalable cloning utility. I'd like to be able to dynamically associate the recently cloned&inserted records with their originals while traversing down the chain of children.

Improve this question

2 Answers 2

Reset to default
8

Almost certainly not; the order is undefined.

Perhaps a silly question, but why not add an order by clause? It sounds like, for your purposes, the order doesn't matter as long as it's consistent. You may be able to just order by id asc or createddate desc.

Improve this answer
6
  • Yes, an ORDER BY clause could work for sure, I hadn't thought about that... How can I take what's in a current List (method input) and re-order it ASC?
    – Nathan Williams
    Commented Sep 17, 2013 at 21:23
  • That's another option; you can ignore the order by clause in favor of your own sorting logic. I would define a static method somewhere that accepts a list of records, either from a query or your cloning utility's input, and sorts them consistently.
    – Jonathan Hersh
    Commented Sep 17, 2013 at 21:25
  • To sort them consistently, do I need to "[SELECT Id FROM sObject WHERE Id IN :inputList ORDER BY Id]"? Or is there a way that does not involve a SoQL query?
    – Nathan Williams
    Commented Sep 17, 2013 at 21:26
  • Yes, that's how you'd sort if you wanted to do it in SOQL. Again, the other option is writing your own sorting logic for an arbitrary list of records as input.
    – Jonathan Hersh
    Commented Sep 17, 2013 at 21:27
  • 1
    As a matter of practicality, salesforce.com "usually" returns a query as if it were ORDER BY Id ASC, which is simply a function of how most databases work when using indices, but, as stated in this answer, there is no guarantee of this ordering, so explicit ordering would be preferred.
    – sfdcfox ♦
    Commented Sep 17, 2013 at 21:30
0

You could also utilize a Map to define the order. In your example you could add to the map the following values

'My opportunity 1' ,1 'My opportunity 2', 2 etc.

Then, when you loop through the results of your query you could use the Opportunity name to retrieve the position in the list of the corresponding record.

Be careful this only works if you have defined a unique key, so probably the name is not good enough... Maybe you can consider using a concatenation of several values i.e Sobject.name#sobject.lastmodifiedby etc.

I think this solution is quite generic and maybe it worths considering

Improve this answer

You must log in to answer this question.

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