Missing `ORDER BY ... NULLS FIRST | LAST`

[martijnthe]

I'm trying to add NULLS FIRST or NULLS LAST to an ORDER BY, but I cannot figure out how to do this. Is this functionality missing?

Any hint on how to best implement it? I'd be more than happy to craft a PR for pypika.

[dszady-rtb]

Hey @martijnthe, did you figure out how to add NULLS FIRST | LAST to the ORDER BY? I am facing the same issue

[pblankley]

Hey @dszady-rtb and @martijnthe I ran into the same issue, and since it looks like the PyPika team at Kayak is no longer maintaining the repo, wrote my own solution by super classing their QueryBuilder (this example is for Snowlfake)

from enum import Enum
from typing import Any, Optional

from pypika import Field, Query, Table
from pypika.dialects import QueryBuilder, SnowflakeQueryBuilder
from pypika.utils import builder, format_quotes



class NullSorting(Enum):
    first = "FIRST"
    last = "LAST"


class SnowflakeQueryBuilderWithOrderByNullsOption(SnowflakeQueryBuilder):
    @builder
    def replace_table(self, current_table: Optional[Table], new_table: Optional[Table]) -> "QueryBuilder":
        """
        Replaces all occurrences of the specified table with the new table. Useful when reusing fields across
        queries.

        :param current_table:
            The table instance to be replaces.
        :param new_table:
            The table instance to replace with.
        :return:
            A copy of the query with the tables replaced.
        """
        self._from = [new_table if table == current_table else table for table in self._from]
        self._insert_table = new_table if self._insert_table == current_table else self._insert_table
        self._update_table = new_table if self._update_table == current_table else self._update_table

        self._with = [alias_query.replace_table(current_table, new_table) for alias_query in self._with]
        self._selects = [select.replace_table(current_table, new_table) for select in self._selects]
        self._columns = [column.replace_table(current_table, new_table) for column in self._columns]
        self._values = [
            [value.replace_table(current_table, new_table) for value in value_list]
            for value_list in self._values
        ]

        self._wheres = self._wheres.replace_table(current_table, new_table) if self._wheres else None
        self._prewheres = self._prewheres.replace_table(current_table, new_table) if self._prewheres else None
        self._groupbys = [groupby.replace_table(current_table, new_table) for groupby in self._groupbys]
        self._havings = self._havings.replace_table(current_table, new_table) if self._havings else None
        # Adding the slot for nulls first/last is the only change here
        self._orderbys = [
            (orderby[0].replace_table(current_table, new_table), orderby[1], orderby[2])
            for orderby in self._orderbys
        ]
        self._joins = [join.replace_table(current_table, new_table) for join in self._joins]

        if current_table in self._select_star_tables:
            self._select_star_tables.remove(current_table)
            self._select_star_tables.add(new_table)

    @builder
    def orderby(self, *fields: Any, **kwargs: Any) -> "QueryBuilder":
        for field in fields:
            field = Field(field, table=self._from[0]) if isinstance(field, str) else self.wrap_constant(field)

            self._orderbys.append((field, kwargs.get("order"), kwargs.get("nulls")))

    def _orderby_sql(
        self,
        quote_char: Optional[str] = None,
        alias_quote_char: Optional[str] = None,
        orderby_alias: bool = True,
        **kwargs: Any,
    ) -> str:
        """
        Produces the ORDER BY part of the query.  This is a list of fields and possibly their
        directionality, ASC or DESC and null sorting option (FIRST or LAST).
        The clauses are stored in the query under self._orderbys as a list of tuples
        containing the field, directionality (which can be None),
        and null sorting option (which can be None).

        If an order by field is used in the select clause,
        determined by a matching, and the orderby_alias
        is set True then the ORDER BY clause will use
        the alias, otherwise the field will be rendered as SQL.
        """
        clauses = []
        selected_aliases = {s.alias for s in self._selects}
        for field, directionality, nulls in self._orderbys:
            term = (
                format_quotes(field.alias, alias_quote_char or quote_char)
                if orderby_alias and field.alias and field.alias in selected_aliases
                else field.get_sql(quote_char=quote_char, alias_quote_char=alias_quote_char, **kwargs)
            )

            if directionality is not None:
                orient = f" {directionality.value}"
            else:
                orient = ""

            if nulls is not None:
                null_sorting = f" NULLS {nulls.value}"
            else:
                null_sorting = ""
            clauses.append(f"{term}{orient}{null_sorting}")

        return " ORDER BY {orderby}".format(orderby=",".join(clauses))


class SnowflakeQuery(Query):
    """
    Defines a query class for use with Snowflake.
    """

    @classmethod
    def _builder(cls, **kwargs) -> SnowflakeQueryBuilderWithOrderByNullsOption:
        return SnowflakeQueryBuilderWithOrderByNullsOption(**kwargs)

Hope this helps you both and anyone else who runs into this limitation. I'm happy to (and would prefer) to open this as a PR if that's an option, but I don't think it is.

[dszady-rtb]

In our case I just created a simple enum to be used instead of pypika.Order

class Order(Enum):
    """
    Enum for ordering in queries.
    Mimics pypika.enums.Order but ads NULLS FIRST and NULLS LAST options.
    """

    asc = "ASC"
    desc = "DESC"
    asc_nulls_first = "ASC NULLS FIRST"
    asc_nulls_last = "ASC NULLS LAST"
    desc_nulls_first = "DESC NULLS FIRST"
    desc_nulls_last = "DESC NULLS LAST"