[Python] Ordering via multiple fields with a case expression

I have been struggling for great amount of time with ordering SQL Alchemy query by two fields, but of them can be empty.

Please check my table.

class Log(Base):
id = Column(BigInteger, primary_key=True)

uuid = Column(UUID(as_uuid=True), nullable=True, server_default=None)
registered = Column(DateTime, nullable=False, server_default=utcnow())


I'm trying to select all elements from the and ordering is crucial for me; I need to order the elements by uuid field however, as you can see, this field can be empty.

In such cases, i.e. when uuid is a None I need to order by registered field which cannot be an empty value.

I tried to solve my problem using the following query

query = (
select(Log)
.order_by(
case(
(Log.sequence_id.is_(None), cast(NotificationLog.logged_at, String)),
else_=cast(NotificationLog.sequence_id, String)
),
NotificationLog.logged_at
)
)


It works but for the scenarios with empty values. To be more specific I creating the following testing scenarios.

"logs, expected_order", # elements in logs: (log_id, microseconds_offset, uuid)
[
# Scenario 1: UUIDs are provided thus ordering is based on them
(
[
(1, 1, uuid7_from_datetime(datetime(2025, 1, 3))),
(2, 2, uuid7_from_datetime(datetime(2025, 1, 2))),
(3, 2, uuid7_from_datetime(datetime(2025, 1, 1))),
],
[3, 2, 1],
),
# # Scenario 2: there are no UUIDs thus ordering is based on logged at timestamp
(
[
(1, 3, None),
(2, 2, None),
(3, 1, None),
],
[3, 2, 1],
),
# # Scenario 3: UUIDs are filled, and they differ just by microseconds
(
[
(1, 0, uuid7_from_datetime(datetime(2025, 1, 1, 12, 0, 0, 3000))), # +3ms
(2, 0, uuid7_from_datetime(datetime(2025, 1, 1, 12, 0, 0, 2000))), # +2ms
(3, 0, uuid7_from_datetime(datetime(2025, 1, 1, 12, 0, 0, 1000))), # +1ms
],
[3, 2, 1],
),
# # Scenario 4: not all UUID are filled
(
[
(1, 0, uuid7_from_datetime(datetime(2025, 1, 1, 12, 0, 0, 8000))), # +8ms
(2, 0, uuid7_from_datetime(datetime(2025, 1, 1, 12, 0, 0, 2000))), # +7ms
(3, 1, None),
(4, 2, None),
],
[3, 4, 2, 1],
),
# # Scenario 5: logs are not ordered and some of the UUIDs are missing
(
[
(1, 5, uuid7_from_datetime(datetime(2025, 1, 1, 12, 0, 0, 8000))),
(2, 10, uuid7_from_datetime(datetime(2025, 1, 1))),
(3, 7, uuid7_from_datetime(datetime(2025, 1, 1))),
(4, 8, None),
],
[3, 1, 4, 2],
),
],
)


For all the scenarios the registered parameter is a frozen date time object with specified microseconds added.

The two last scenarios fail.

I would really appreciate any help in solving my problem.strong text

Continue reading...