add nullsfirst() and nullslast() column ordering operators

[Mike Bayer]

OK just to double check, the syntax looks like:

SELECT * FROM sometable ORDER BY foo NULLS FIRST

SELECT * FROM sometable ORDER BY foo DESC NULLS LAST

Yes.

?  i.e. is "DESC"/"ASC" before the "NULLS" part ?  or doesn't matter ?

It does matter according to Firebird Null Guide:
http://www.firebirdsql.org/manual/nullguide-sorts.html

[Former user]

Just so you don't think nobody needs this anyway: I'd really be happy if this could be implemented, I need it quite frequently.

[Mike Bayer]

if you'd like to try your hand at a patch + unit test its fairly straightforward...otherwise we'll get to it at some point...

[Former user]

PostgreSQL uses the same syntax, as well:

ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] ...

http://www.postgresql.org/docs/current/static/sql-select.html#SQL-ORDERBY

[Mike Bayer]

switching this to "volunteer" for now but we may still get to it at some point.

[Former user]

With a little mentoring I might work in this bug and try to provide a patch for it. Do you see it possible, zzzeek? (since I can't find how to create an account, I will keep checking back for updates).

Regards, Mariano

[Mike Bayer]

I can add your email as a CC here. Sure, patches are welcome against the latest tip if they include unit tests.

[Former user]

with unittest is the only way I work :) My email is mariano.mara at gmail dot com. Let me see if I can understand what's going on under the hood. I will add my questions here.

[Former user]

Suddenly, I need this feature too.

Any news from Mariano?

If none, zzzeek, could you please point some starting points: where to begin, where to put code, similar places, tests?

[Former user]

Replying to guest:

Suddenly, I need this feature too.

Any news from Mariano?

If none, zzzeek, could you please point some starting points: where to begin, where to put code, similar places, tests? Please add [e-mail] as a CC.

[Mike Bayer]

nullsfirst/nullslast would probably be implemented the same way desc() and asc() are implemented - as a unary operator. If you dig through the implementation of asc/desc, it would almost be a copy-and-paste job. tests would be in test/sql/test_selectable.py and test/sql/test_query.py (since these are standard SQL operators).

[Former user]

Sorry for lack of updates, I started working on it but got dragged by work. Still in my TODO list though.

Mariano

[Former user]

Up again! =)

Made an attempt to implement nullsfirst and nullslast. Didn't test on a real database yet, but something bothers me and I'd like to ask. As mentioned above "NULLS FIRST" modifier must be the last one in "ORDER BY MYTABLE.MYCOLUMN DESC NULLS LAST", but my straightforward implementation allows this:

table2.select(order_by=desc(nullsfirst(table2.c.otherid)))

and produces this:

SELECT myothertable.otherid, myothertable.othername 
FROM myothertable ORDER BY myothertable.otherid NULLS FIRST DESC

Is it ok and we leave this issue to the user to use that properly?

[e-mail]

[Mike Bayer]

the slicker way would be if desc() and asc() received nullsfirst/nullslast and returned an appropriate structure. here's where everything in expression.py being a function helps.

[Former user]

Do you mean something like that?

def desc(column, nulls = None):
    """Return a descending ``ORDER BY`` clause element.
    Optional argument can be one of 'first' or 'last' to produce ``NULLS LAST`` or ``NULLS FIRST`` clause.
    e.g.::

      order_by = [nulls = 'first')](desc(table1.mycol,)

    """
    column = _UnaryExpression(column, modifier=operators.desc_op)

    if nulls == 'first':
        column = nullsfirst(column)
    elif nulls == 'last':
        column = nullslast(column)
    return column

or like that

def desc(column, modifier = None):
    """Return a descending ``ORDER BY`` clause element.

    e.g.::

      order_by = [modifier = nullsfirst)](desc(table1.mycol,)

    """
    column = _UnaryExpression(column, modifier=operators.desc_op)

    if modifier is not None:
        column = modifier(column)
    return column

[Mike Bayer]

I mean have the desc() unwrap what it gets and fix the ordering, like:

def desc(expr):
    if expr_is_a_nullsfirst(expr):
        return nullsfirst(desc(get_the_nullsfirst(expr))
    elif ....

ultimately I think people would usually be calling this as col.desc().nullsfirst()

[Michael Trier]

How's this for a patch?

[Michael Trier]

Fixed in 15ea17d7f882fec3f892a22612da4827780c8dae

[Mike Bayer]

Removing milestone: 0.7.0 (automated comment)