custom ordering of select results

[Dave S]

Way back when, I asked a similar question:

<URL:https://groups.google.com/d/topic/web2py/bnuall8tsgc/discussion>

I have a different application, where I want to order the rows according to a string field, where the string field has a prefix and a suffix, and the suffix is the dominant part of the ordering:

4321A < 0123B,
2345D < 5432D

etc

It's really easy to write a custom cmp() for this, and then to do sorted(rows, mycmp), but the result is a list, not a Rows object; in particular, it loses the colnames.  If  I do 

db(query1).select().sort(mycmp),

that fails because mycmp() requires 2 arguments, but 1 given.

The example of sort() in the book doesn't mind having only 1 argument:

The use of orderby='<random>' is not supported on Google NoSQL. However, in this situation and likewise in many others where built-ins are insufficient, imports can be used:

1 2	import random rows=db(...).select().sort(lambda row: random.random())

<URL:http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#orderby--groupby--limitby--distinct--having-orderby_on_limitby-left-cache>

So how do I use sort() when the comparison requires 2 arguments?

(I'm using sqlite3 for this project, so I'm not ready to think about putting a procedure on the db server.)

Or if I use sorted(), how do I turn list back into a Rows object?  (Okay, there's a work-around for this ... write the view to handle a list, rather than having a default view, but one of these days I'm bound to need a Rows object.)

/dps

[Anthony]

I have a different application, where I want to order the rows according to a string field, where the string field has a prefix and a suffix, and the suffix is the dominant part of the ordering:

4321A < 0123B,
2345D < 5432D

The simplest and most efficient method is probably to let the database do the sorting. In SQLite, you can use the substr() function:

rows = db(query).select(..., orderby='substr(mytable.myfield, -1), mytable.myfield')

substr(mytable.myfield, -1) selects the last character of myfield (use -2 for the last 2 characters, etc.). Adding mytable.myfield to the orderby breaks any ties by ordering based on the beginning characters of the field.

 

It's really easy to write a custom cmp() for this, and then to do sorted(rows, mycmp), but the result is a list, not a Rows object; in particular, it loses the colnames.  If  I do 

db(query1).select().sort(mycmp),

that fails because mycmp() requires 2 arguments, but 1 given.

The "key" function passed to the Python sorted() function should take only a single argument -- an element from the iterator being sorted. If your mycmp requires two arguments, then it should fail with sorted() as well as with Rows.sort(). Just rewrite mycmp so it takes only one argument (a Row object).

 

Or if I use sorted(), how do I turn list back into a Rows object?  (Okay, there's a work-around for this ... write the view to handle a list, rather than having a default view, but one of these days I'm bound to need a Rows object.)

Try:

rows.records = sorted(rows, mycmp)

Though, as noted above, rows.sort(mycmp) should work just as well (it uses sorted() internally).

Anthony

[Dave S]

On Wednesday, March 21, 2018 at 5:30:08 AM UTC-7, Anthony wrote:

I have a different application, where I want to order the rows according to a string field, where the string field has a prefix and a suffix, and the suffix is the dominant part of the ordering:

4321A < 0123B,
2345D < 5432D

The simplest and most efficient method is probably to let the database do the sorting. In SQLite, you can use the substr() function:

rows = db(query).select(..., orderby='substr(mytable.myfield, -1), mytable.myfield')

substr(mytable.myfield, -1) selects the last character of myfield (use -2 for the last 2 characters, etc.). Adding mytable.myfield to the orderby breaks any ties by ordering based on the beginning characters of the field.

That looks pretty straightforward, once I realize the substr() function is available   =8-0
 

 

It's really easy to write a custom cmp() for this, and then to do sorted(rows, mycmp), but the result is a list, not a Rows object; in particular, it loses the colnames.  If  I do 

db(query1).select().sort(mycmp),

that fails because mycmp() requires 2 arguments, but 1 given.

The "key" function passed to the Python sorted() function should take only a single argument -- an element from the iterator being sorted. If your mycmp requires two arguments, then it should fail with sorted() as well as with Rows.sort(). Just rewrite mycmp so it takes only one argument (a Row object).

Ah, the "key" argument.  That's what's going on.  sorted() has both a cmp and key argument, and I viewed this as a cmp problem.
 

 

Or if I use sorted(), how do I turn list back into a Rows object?  (Okay, there's a work-around for this ... write the view to handle a list, rather than having a default view, but one of these days I'm bound to need a Rows object.)

Try:

rows.records = sorted(rows, mycmp)

Though, as noted above, rows.sort(mycmp) should work just as well (it uses sorted() internally).

Anthony

Thanks, it sounds like I was focusing on the wrong area.  I guess I should have peeked in at rows.sort() to see how it worked.  As always, you're on top of the game.

/dps


 

[Anthony]

The "key" function passed to the Python sorted() function should take only a single argument -- an element from the iterator being sorted. If your mycmp requires two arguments, then it should fail with sorted() as well as with Rows.sort(). Just rewrite mycmp so it takes only one argument (a Row object).

Ah, the "key" argument.  That's what's going on.  sorted() has both a cmp and key argument, and I viewed this as a cmp problem.

Yeah, "key" is preferred since Python 2.4, and that is what Rows.sort() uses internally. See https://docs.python.org/2/howto/sorting.html#the-old-way-using-the-cmp-parameter. If you need that same mycmp function elsewhere so cannot simply rewrite to work as the "key" argument, you can use functools.cmp_to_key() to convert it to a "key" function.

Anthony

[Dave S]

Finally got to try this out, and of course tried both the substr() and the records =  versions.  SQLite3's substr() uses the 3rd argument differently than Python does (length, rather than endpoint), but once I got that sorted out everything was good!

Thanks again.

/dps "yes, the example was simplified ... omitted the penultimate character"