nulls_last() in sqlalchemy is expanding the labelled column

Question

In the following snippet I am creating a labelled expression and then using that expression inside of the order_by clause and the select clause. Normal order_by is working with the label but when I use the nulls_last() method, it is expanding the label again instead of using the previously provided column name.

priceSum = func.sum(Book.price).label("price_sum")

# makes use of the labelled 'price_sum' column
print(select(Book.author, priceSum).group_by(Book.author).order_by(priceSum))

# but this expands the priceSum expression instead of using 'price_sum' label
print(select(Book.author, priceSum).group_by(Book.author).order_by(priceSum.nulls_last()))

Following are the SQL queries generated by the above snippet:

-- without nulls_last()
SELECT books.author, sum(books.price) AS price_sum 
FROM books GROUP BY books.author ORDER BY price_sum

-- with nulls_last()
SELECT books.author, sum(books.price) AS price_sum 
FROM books GROUP BY books.author ORDER BY sum(books.price) NULLS LAST

Reproducible Code:

from sqlalchemy import (
    create_engine,
    Column,
    Integer,
    String,
    Float,
    func,
    nulls_last,
    select,
    text,
)
from sqlalchemy.orm import sessionmaker, declarative_base

# Create the engine to connect to the database
engine = create_engine("sqlite://")

# Create a Base class that will be the base for our declarative class mappings.
Base = declarative_base()


class Book(Base):
    __tablename__ = "books"
    id = Column(Integer, primary_key=True)
    title = Column(String)
    author = Column(String)
    price = Column(Float)


# This will create the table 'books' in the database if it doesn't exist.
Base.metadata.create_all(engine)

# Create a session to interact with the database
Session = sessionmaker(bind=engine)
session = Session()

# Add some data to the table
book1 = Book(title="Book 1", author="Author 1", price=29.99)
book2 = Book(title="Book 2", author="Author 2", price=19.99)
book3 = Book(title="Book 3", author="Author 1", price=24.99)

# Add the books to the session and commit the changes to the database
session.add_all([book1, book2, book3])
session.commit()

priceSum = func.sum(Book.price).label("price_sum")
print("\n-- Without nulls_last(): \n")
print(select(Book.author, priceSum).group_by(Book.author).order_by(priceSum), "\n")
print("\n-- With nulls_last(): \n")
print(
    select(Book.author, priceSum).group_by(Book.author).order_by(priceSum.nulls_last()),
    "\n",
)
print("\n-- Using text() solves the issue: ")
print(
    select(Book.author, priceSum)
    .group_by(Book.author)
    .order_by(nulls_last(text("price_sum"))),
    "\n",
)

NOTE: I am using order_by(nulls_last(text('price_sum'))) to overcome this for now,so I am looking for some other ways to solve this

Answer 1

If we have any labelled columns in the SELECT clause that we want to use in other places, then SQLAlchemy Docs suggests to use the label name directly inside of order_by or group_by, without using any text, if a column with that label doesn't exist then it'll raise an error, otherwise it will simply make use of the provided label name.

Following snippet makes use of this:

priceSum = func.sum(Book.price).label("price_sum")
print(
    select(Book.author, priceSum)
    .group_by(Book.author)
    .order_by(nulls_last(desc("price_sum")))
)

and we get the following SQL query which makes use of the label price_sum instead of recreating the sum calculation:

SELECT books.author, sum(books.price) AS price_sum 
FROM books GROUP BY books.author ORDER BY price_sum DESC NULLS LAST

Reproducible Code:

from sqlalchemy import (
    create_engine,
    Column,
    Integer,
    String,
    Float,
    func,
    nulls_last,
    select,
    text,
    desc,
)
from sqlalchemy.orm import sessionmaker, declarative_base

# Create the engine to connect to the database
engine = create_engine("sqlite://")

# Create a Base class that will be the base for our declarative class mappings.
Base = declarative_base()


class Book(Base):
    __tablename__ = "books"
    id = Column(Integer, primary_key=True)
    title = Column(String)
    author = Column(String)
    price = Column(Float)


# This will create the table 'books' in the database if it doesn't exist.
Base.metadata.create_all(engine)

# Create a session to interact with the database
Session = sessionmaker(bind=engine)
session = Session()

# Add some data to the table
book1 = Book(title="Book 1", author="Author 1", price=29.99)
book2 = Book(title="Book 2", author="Author 2", price=19.99)
book3 = Book(title="Book 3", author="Author 1", price=24.99)

# Add the books to the session and commit the changes to the database
session.add_all([book1, book2, book3])
session.commit()

priceSum = func.sum(Book.price).label("price_sum")
print("\n-- Without nulls_last(): \n")
print(select(Book.author, priceSum).group_by(Book.author).order_by(priceSum), "\n")
print("\n-- With nulls_last(): \n")
print(
    select(Book.author, priceSum).group_by(Book.author).order_by(priceSum.nulls_last()),
    "\n",
)
print("\n-- Using text() solves the issue: ")
print(
    select(Book.author, priceSum)
    .group_by(Book.author)
    .order_by(nulls_last(text("price_sum"))),
    "\n",
)

print("\n-- Providing the label name as suggested in SQLAlchemy Docs: ")
print(
    select(Book.author, priceSum)
    .group_by(Book.author)
    .order_by(nulls_last(desc("price_sum")))
)