X

It's All About the Platform.

Using Sorting In Groovy Queries

Richard Bingham
Senior Development Manager

In addition to the other features we've looked at when using the NewView() features for querying data using groovy, there is another useful option to mention - sorting of the returned rows. By using the setSortBy() method you specify the attribute upon which your query will order the returned records. This also supports multiple sort attributes and asc/desc keywords.

Note that the set setSortBy() method is inherited from the underlying ViewObject class and there are restrictions on the methods inherited, for example the similar setOrderByClause() cannot be used.  Also be aware that the sorting occurs in-memory (synchronously in the users session) and as such if there are a great many rows returned it may result in performance issues. As such always use a ViewCriteria and even limit the number of rows returned as shown in the example below. For more details and other examples of setSortBy() consult the groovy scripting guide.

Illustrative Example

Users want to see the titles of the most recent Opportunities a customer has open, so they can track recent activity. Rather than click on the Opportunities tab in the Accounts page, they'd like this information on the front page. As such we added a custom Formula Field to the Accounts object, and used a groovy script to run a query. Here is how it looks:

The query matches the Account name on the opportunities to ensure a small data set is returned. Then it uses the setSortBy() method to organize them by the creation date - in a descending order. Finally it ensures only the first 5 records are fetched from the database. After executing the query, it then returns the values as a concatenated string to the formula field.

def myString = ""
def myComp = UniqueNameAlias
def vo = newView('OpportunityVO')
def vc = newViewCriteria(vo)
def vcr = vc.createRow()
def vci = vcr.ensureCriteriaItem('TargetPartyName')
    vci.setOperator('=')
    vci.setValue(myComp)
   vc.insertRow(vcr)
   vo.appendViewCriteria(vc)
   vo.setSortBy('OptyCreationDate desc')
   vo.setMaxFetchSize(5)
   vo.executeQuery()
 while(vo.hasNext()) {
  def row = vo.next() 
  myString = row.Name + '\n' + myString
 }
return myString

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.