With an Ordered Result
We want to retrieve the data in alphabetical order according to employee last name.
An Optic query like this one retrieves a row sequence containing all view columns with rows in ascending order by the value of a specified column:
op.fromView('Employee', 'Profile')
.orderBy(op.asc('Surname'))
.offsetLimit(0, 100)
.result();
We used this query to retrieve a row sequence of all 23 columns defined in our view for each employee with rows in ascending order by Surname. We limited our output to the first 100 results:
The Data Accessor Function
fromView()pulls data indexed for the viewProfileassociated with the schemaEmployeeinto a row sequence of this view’s columns.The Operator Function
orderBy()sorts its input row sequence into the order specified.The Auxiliary Function
asc()explicitly sorts rows in ascending order by the specified column's value. The default order is ascending. The Auxiliary Functiondesc()sorts in descending order.The Operator Function
offsetLimit()restricts results returned. The first parameter specifies the number of results to skip; the second, the number of results to return. So, (0, 100) returns the first 100 results.The Executor Function
result()executes the query and returns the results as a row sequence.
Here are row 1 and part of row 2 of the 100-row x 23-column result:
{
"Employee.Profile.GUID": "523b80e5-b98b-46d4-b74f-a755e071e05b",
"Employee.Profile.HiredDate": "2017-12-09",
"Employee.Profile.Gender": "female",
"Employee.Profile.Title": "Mrs.",
"Employee.Profile.GivenName": "Jean",
"Employee.Profile.MiddleInitial": "T",
"Employee.Profile.Surname": "Abbey", // Surname: Abbey
"Employee.Profile.StreetAddress": "1878 School Street",
"Employee.Profile.City": "Wilton",
"Employee.Profile.State": "CT",
"Employee.Profile.ZipCode": "6897",
"Employee.Profile.Country": "US",
"Employee.Profile.EmailAddress": "JeanTAbbey@rhyta.com",
"Employee.Profile.TelephoneNumber": "203-761-3316",
"Employee.Profile.TelephoneCountryCode": "1",
"Employee.Profile.Birthday": "2/15/62",
"Employee.Profile.NationalID": "041-03-4433",
"Employee.Profile.Point": "41.232025,-73.441277",
"Employee.Profile.BaseSalary": 72334,
"Employee.Profile.Bonus": 7233,
"Employee.Profile.Department": "Marketing",
"Employee.Profile.Status": "Active - Regular Exempt (Full-time)",
"Employee.Profile.ManagerGUID": "d0862e5d-5be6-473b-a500-3e3a852b2bb8"
}
{
"Employee.Profile.GUID": "71ea8757-e12e-45a9-bd42-a576b431812e",
"Employee.Profile.HiredDate": "2012-08-19",
"Employee.Profile.Gender": "female",
"Employee.Profile.Title": "Mrs.",
"Employee.Profile.GivenName": "Donna",
"Employee.Profile.MiddleInitial": "J",
"Employee.Profile.Surname": "Acevedo" // Surname: Acevedo
// ... (other 16 columns)
}
This query returned the first 100 results as we specified in
offsetLimit().The rows are in alphabetical order according to the
Surnamecolumn.The columns are presented in the order we defined them in our view.
To change the order for this query alone, put all the columns into the
select()operator function in your desired order.To permanently change the column order, edit your TDE to change their order in your view.
To restrict the columns returned, use the
select()operator function.