Sorting Using Python Sorted Function

[Robert_Hrynciw]

Hello,

I recently created a JSON table with dropdowns inside of it. It is sorted certain ways with a SQL binding on page launch. When using a system.db.runNamedQuery script to return values based on different filters, I have to finish with this script in order to return it as a JSON object for the table:

	returnedData = system.dataset.toPyDataSet(system.db.runNamedQuery(query, params))

	NewDict = {'%s' % i: 
		{colName: value
        for colName, value in zip(returnedData.columnNames, list(row))}
   		for i, row in enumerate(returnedData)}

	self.getSibling("Table").props.data = NewDict.values()

I need to figure out how I can sort a table by a certain column in ascending or descending order (extra credit for a sort by another column after the first sort).

So, for example this table is sorted by Hours ascending through a SQL binding returned as JSON..

If I am to select a craft of "Electricians" in my dropdown filter, I get this return...

As you can see, it's sorting by Clock Number ascending. The script for this is as follows with just "sorted" in front of NewDict.values()...

		NewDict = {'%s' % i: 
			{colName: value
	        for colName, value in zip(returnedData.columnNames, list(row))}
	   		for i, row in enumerate(returnedData)}
		self.getSibling("Table").props.data = sorted(NewDict.values())

Thank you in advance for your help.

[PGriffith]

sorted accepts a keyword argument key:
https://docs.python.org/2.7/library/functions.html#sorted

key should be a callable that returns a sortable item per value. Typically this is a simple lambda. Simplifying your script a little, and running it on some test data, you can return a sorted list with something like this:

returnedData = system.dataset.toPyDataSet(system.dataset.toDataSet(
		["Clock Number", "Name", "Department", "Hours"], 
		[
			["100003683", "Bob Vance", 9, 24.0],
			["100008031", "Jim Hopper", 9, 32.6],
			["100012345", "Amos Burton", 1, 40],
		]
	))
	
	return sorted(
		dict(zip(returnedData.columnNames, list(row)))
		for row in returnedData,
		key = lambda columns: columns['Hours']
	)

The lambda is a function that will be called for each item in the comparison. It accepts the single argument in the list (in this case, the dictionary per row); the lambda is declaring this as being named columns. Then columns['Hours'] is returning the value of the 'Hours' column, which will be used for the sorting operation. If you wanted to sort by multiple values, you would return a tuple with each value to sort by, in order, e.g.:
key = lambda columns: (columns['Department'], columns['Hours'])
Where the return value will first be sorted by department, then by hours.

[Robert_Hrynciw]

Thank you very much! Saved me a lot of confusion in your explanation. I had seen docs but couldn't quite figure out what I was doing wrong with the key. I have one table where I sort a special way with the binding query...

SELECT Lname, Fname, EID, Shift, ApprenticeGrp, Dept, CurrentHours, FirstShift as [Midnights], SecondShift as [Days], ThirdShift as [Afternoons], Status, ApprovedShift, ApprovedSecondShift
FROM [Maintenance].[dbo].[MOS_Signup]
WHERE TradeSkill = 0 and DateTime = :Date
ORDER BY CASE WHEN ApprenticeGrp = 0 THEN ApprenticeGrp END desc, ApprenticeGrp desc, CurrentHours desc

Where I order by ApprenticeGrp 0 at the top, then ApprenticeGrp descending (5-1) then by CurrentHours descending. Is this possible? I know you can put them in reverse order (not sure how to do it with multiple column sorting, though) but I don't know if you can case as well.

[PGriffith]

Probably something like:

key = lambda columns: (1 if column['ApprenticeGroup'] == 0 else 0, column['ApprenticeGroup'], column['CurrentHours']),
reverse = True,

You might also be able to use something like not bool(column['ApprenticeGroup']), if you wanted to get fancy. But basically, for each row in your data you want to return a three-item tuple:

Value	Key 1	Key 2	Key 3
{ApprenticeGroup: 0, CurrentHours: 20}	1	0	20
{ApprenticeGroup: 0, CurrentHours: 10}	1	0	10
{ApprenticeGroup: 5, CurrentHours: 10}	0	5	10
{ApprenticeGroup: 4, CurrentHours: 10}	0	4	10
{ApprenticeGroup: 1, CurrentHours: 10}	0	1	10

Which should sort the way you want. Remember that you can always use a simple negation if something isn't sorting the way you want; e.g. -columns['ApprenticeGrp'] would give you the opposite result for a particular column in an otherwise ascending or descending sort.

[Robert_Hrynciw]

Thank you very much, this solved my question! You're a lifesaver.