function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Rebecca VandersliceRebecca Vanderslice 

SOQL Order By with records owned by current user showing first but other records are still in the list

I want to show the results of a SOQL query to a user including those records that are not owned by the user.  But with the current user owned records appearing first in the list.  Then within the group of records owned by the current user, order by a value field.

Is there a creative way to put a temp column into the SOQL so that i can order by 1 or 2 first and then by value? Here's what we are doing currently but i want to try to eliminate have to create 2 queries.
  
private List<Site_Account__c> createSiteAccountsList() {

List<Site_Account__c> siteAccountsOwnedByLoggedInUser = 
[SELECT TotalValue__c, Owner.Name, OwnerId, Name, Contact_Id__r.Name, Contact_Id__c, AccountDID__c, Currency__c, AccountType__c
  FROM Site_Account__c 
  WHERE Account_Id__c =: selectedAccountId AND OwnerId =: UserInfo.getUserId()
  ORDER BY TotalValue__c DESC LIMIT 1000];

 List<Site_Account__c> siteAccountsNotOwnedByLoggedInUser = 
[SELECT TotalValue__c, Owner.Name, OwnerId, Name, Contact_Id__r.Name, Contact_Id__c, AccountDID__c, Currency__c, AccountType__c
   FROM Site_Account__c 
   WHERE Account_Id__c =: selectedAccountId AND OwnerId !=: UserInfo.getUserId()
   ORDER BY OwnerId, TotalValue__c DESC LIMIT 1000];

        List<Site_Account__c> siteAccounts = new List<Site_Account__c>();
       
        siteAccounts.addAll(siteAccountsOwnedByLoggedInUser);
        siteAccounts.addAll(siteAccountsNotOwnedByLoggedInUser);
        return siteAccounts;
    }
Any creative ideas are welcome!
 
Best Answer chosen by Rebecca Vanderslice
Andy BoettcherAndy Boettcher
I'll build right on top of Kevin's comment - if you make a checkbox formula field on the Site_Account__c object that doesn't have anything else but "IF(OwnerId = User.Id, true, false)" (that's pseudocode, don't take it verbatim) then you could just order by that.

All Answers

kevin lamkevin lam
Have you considered creating a custom formula field to store whether the current user is the record owner and then order by that field first?
Andy BoettcherAndy Boettcher
I'll build right on top of Kevin's comment - if you make a checkbox formula field on the Site_Account__c object that doesn't have anything else but "IF(OwnerId = User.Id, true, false)" (that's pseudocode, don't take it verbatim) then you could just order by that.
This was selected as the best answer
Rebecca VandersliceRebecca Vanderslice
Thanks for the suggestions.  We can definitely look into doing it that way.  I was really hoping to avoid having to createa  permanent field on the object just for this one use.  But I'll see if we can give it a try!
kevin lamkevin lam
You don't need an IF statement for the formula field, just:

$User.Id = OwnerId