Source code for sqlalchemy_utils.functions.sort_query

import sqlalchemy as sa

from .database import has_unique_index
from .orm import _get_query_compile_state, get_tables


[docs]def make_order_by_deterministic(query): """ Make query order by deterministic (if it isn't already). Order by is considered deterministic if it contains column that is unique index ( either it is a primary key or has a unique index). Many times it is design flaw to order by queries in nondeterministic manner. Consider a User model with three fields: id (primary key), favorite color and email (unique).:: from sqlalchemy_utils import make_order_by_deterministic query = session.query(User).order_by(User.favorite_color) query = make_order_by_deterministic(query) print query # 'SELECT ... ORDER BY "user".favorite_color, "user".id' query = session.query(User).order_by(User.email) query = make_order_by_deterministic(query) print query # 'SELECT ... ORDER BY "user".email' query = session.query(User).order_by(User.id) query = make_order_by_deterministic(query) print query # 'SELECT ... ORDER BY "user".id' .. versionadded: 0.27.1 """ order_by_func = sa.asc try: order_by_clauses = query._order_by_clauses except AttributeError: # SQLAlchemy <1.4 order_by_clauses = query._order_by if not order_by_clauses: column = None else: order_by = order_by_clauses[0] if isinstance(order_by, sa.sql.elements._label_reference): order_by = order_by.element if isinstance(order_by, sa.sql.expression.UnaryExpression): if order_by.modifier == sa.sql.operators.desc_op: order_by_func = sa.desc else: order_by_func = sa.asc column = list(order_by.get_children())[0] else: column = order_by # Skip queries that are ordered by an already deterministic column if isinstance(column, sa.Column): try: if has_unique_index(column): return query except TypeError: pass base_table = get_tables(_get_query_compile_state(query)._entities[0])[0] query = query.order_by( *(order_by_func(c) for c in base_table.c if c.primary_key) ) return query