Сортировка и лимиты: order_by, limit

До сих пор наши выборки возвращали строки в «каком-то» порядке — и мы послушно добавляли order_by, когда порядок был важен. Пора признать: без явной сортировки порядка нет вообще. База хранит строки как ей удобно, и хотя на маленькой учебной таблице вывод обычно совпадает с порядком вставки, обещать это никто не может. Любой интерфейс, где данные показываются человеку, обязан задавать порядок сам — и ограничивать размер выдачи, потому что листать тысячу строк никто не будет.

Сегодня три метода, которые добавляются к уже знакомому select как приправы: order_by выстраивает строки, limit ограничивает количество, offset пропускает начало. По отдельности — простые инструменты, вместе — топы, ленты и постраничные списки, на которых держится половина интерфейсов в мире. Лента новостей, история заказов, список товаров с фильтрами — всюду один и тот же скелет: «вот столько, вот в таком порядке, начиная с вот этого места».

Отдельный бонус урока — экономия на переносе данных: база отдаёт ровно столько строк, сколько попросили, и в уже упорядоченном виде. Python не получает тысячу записей, чтобы показать десять, и не сортирует их своими силами. Чем дальше ты в разработке, тем яснее становится: чем меньше работы делает Python с данными, которые мог бы сделать сам движок, тем быстрее работает приложение.

order_by: порядок по вкусу

Начнём с азов: вывести задачи в алфавитном порядке. Метод order_by принимает колонку — по возрастанию это поведение по умолчанию. Всего один вызов — и строки приедут отсортированными, сколько бы их ни было: база сортирует данные на своих скоростях, по индексам и буферам, недоступным Python-коду.

from sqlalchemy import create_engine, String, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase):
    pass

class Task(Base):
    __tablename__ = "tasks"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(80))
    done: Mapped[int] = mapped_column(default=0)
    points: Mapped[int] = mapped_column(default=0)

engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)

data = [
    ("автотесты", 5, 0),
    ("багрепорт", 3, 1),
    ("верстка", 8, 0),
    ("документация", 2, 0),
    ("отчет", 6, 1),
    ("презентация", 1, 0),
]
with Session(engine) as s:
    s.add_all([Task(title=t, points=p, done=d) for t, p, d in data])
    s.commit()

    titles = [t.title for t in s.scalars(select(Task).order_by(Task.title))]
    print(titles)

['автотесты', 'багрепорт', 'верстка', 'документация', 'отчет', 'презентация']

Сортировка случилась в базе: строки приехали в Python уже упорядоченными, и Python лишь собрал названия в список. По умолчанию порядок возрастающий; чтобы перевернуть его, у колонки есть метод desc() — от descending, по убыванию. А если первых ключей мало, order_by принимает несколько колонок: сортировка идёт по первому, при равенстве — по второму.

Как именно база сравнивает строки? По правилам сортировки своей колонки: SQLite по умолчанию сравнивает байты UTF-8, PostgreSQL — по правилам локали базы. Для большинства случаев результат одинаковый — «а» раньше «б», — но экзотические символы и регистр могут отличаться между базами. Если в данных намешаны заглавные и строчные буквы, а порядок нужен человеческий, сортируй по нормализованной колонке или выражению — и проверяй вывод на своих данных, а не на словах документации.

from sqlalchemy import create_engine, String, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase):
    pass

class Task(Base):
    __tablename__ = "tasks"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(80))
    done: Mapped[int] = mapped_column(default=0)
    points: Mapped[int] = mapped_column(default=0)

engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)

data = [
    ("автотесты", 5, 0),
    ("багрепорт", 3, 1),
    ("верстка", 8, 0),
    ("документация", 2, 0),
    ("отчет", 6, 1),
    ("презентация", 1, 0),
]
with Session(engine) as s:
    s.add_all([Task(title=t, points=p, done=d) for t, p, d in data])
    s.commit()

    rows = s.scalars(
        select(Task).order_by(Task.points.desc(), Task.title)
    ).all()
    print([(t.title, t.points) for t in rows])

[('верстка', 8), ('отчет', 6), ('автотесты', 5), ('багрепорт', 3), ('документация', 2), ('презентация', 1)]

Баллы выстроились от восьми к единице, а второй ключ — title — остался про запас: если бы две задачи набрали поровну, между собой они отсортировались бы по алфавиту, а не в случайном порядке. Правило надёжных отчётов: последний ключ сортировки должен быть уникальным — id или title — иначе равные строки встанут непредсказуемо. Это тот же принцип стабильной сортировки, что и в Python-списках, только ответственность лежит на тебе.

Order_by прекрасно соседствует с join из урока 11: сортировать можно и по колонкам связанной таблицы — «задачи по имени автора» записывается как select(Task).join(User).order_by(User.name, Task.title). Сортировка по чужой таблице — первый признак, что в запросе уже есть соединение, и наоборот: добавил join — не забудь про порядок, который нужен пользователю, а не базе.

limit: режем выдачу

Порядок готов — теперь можно честно говорить о «первых» и «последних». Метод limit(n) оставляет в результате только n первых строк уже отсортированного списка. Это второй по частоте спутник сортировки: в живых приложениях почти не встречаются запросы «дай вообще всё», зато «дай первые двадцать в таком-то порядке» — на каждом углу.

from sqlalchemy import create_engine, String, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase):
    pass

class Task(Base):
    __tablename__ = "tasks"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(80))

engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)

data = ["автотесты", "багрепорт", "верстка", "документация", "отчет", "презентация"]
with Session(engine) as s:
    s.add_all([Task(title=t) for t in data])
    s.commit()

    titles = [t.title for t in s.scalars(
        select(Task).order_by(Task.title).limit(3)
    )]
    print(titles)

['автотесты', 'багрепорт', 'верстка']

Limit — не про удобство, а про ресурсы: лента интерфейса не показывает тысячу записей сразу, а база рада отдать три строки вместо шести тысяч. В паре с сортировкой limit превращается в главный запрос реальных сервисов — топ-N: «покажи три самых тяжёлых задачи». Сортируем по убыванию баллов, отрезаем три штуки — база сделала и то и другое.

from sqlalchemy import create_engine, String, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase):
    pass

class Task(Base):
    __tablename__ = "tasks"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(80))
    points: Mapped[int] = mapped_column(default=0)

engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)

data = [("автотесты", 5), ("багрепорт", 3), ("верстка", 8),
        ("документация", 2), ("отчет", 6), ("презентация", 1)]
with Session(engine) as s:
    s.add_all([Task(title=t, points=p) for t, p in data])
    s.commit()

    titles = [t.title for t in s.scalars(
        select(Task).order_by(Task.points.desc()).limit(3)
    )]
    print("топ-3:", titles)

топ-3: ['верстка', 'отчет', 'автотесты']

Топ-N — это всегда связка из двух методов: порядок задаёт смысл («самые тяжёлые»), limit задаёт размер. Если из топа нужны не все, а только, скажем, невыполненные задачи, фильтр where из урока 7 встраивается в ту же цепочку — порядок методов читается как конвейер: отобрали, отсортировали, отрезали.

from sqlalchemy import create_engine, String, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase):
    pass

class Task(Base):
    __tablename__ = "tasks"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(80))
    done: Mapped[int] = mapped_column(default=0)
    points: Mapped[int] = mapped_column(default=0)

engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)

data = [
    ("автотесты", 5, 0),
    ("багрепорт", 3, 1),
    ("верстка", 8, 0),
    ("документация", 2, 0),
    ("отчет", 6, 1),
    ("презентация", 1, 0),
]
with Session(engine) as s:
    s.add_all([Task(title=t, points=p, done=d) for t, p, d in data])
    s.commit()

    titles = [t.title for t in s.scalars(
        select(Task)
        .where(Task.done == 0)
        .order_by(Task.points.desc())
        .limit(2)
    )]
    print(titles)

['верстка', 'автотесты']

В order_by можно класть и агрегаты — так собираются рейтинги. «Кто самый нагруженный» — это отчёт из урока 12 с двумя приправами: сортировка по count по убыванию и limit(1). Агрегация собирает цифры, сортировка выстраивает их, лимит отбирает победителя — три урока в одном запросе.

from sqlalchemy import create_engine, ForeignKey, String, select, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session, relationship

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(40))
    tasks = relationship("Task", back_populates="user")

class Task(Base):
    __tablename__ = "tasks"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(80))
    user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
    user = relationship("User", back_populates="tasks")

engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)

with Session(engine) as s:
    anna = User(name="Анна")
    boris = User(name="Борис")
    s.add_all([anna, boris])
    s.flush()
    s.add_all([
        Task(title="отчёт", user=anna),
        Task(title="багрепорт", user=anna),
        Task(title="презентация", user=anna),
        Task(title="письмо", user=boris),
    ])
    s.commit()

    rows = s.execute(
        select(User.name, func.count(Task.id))
        .join(User.tasks)
        .group_by(User.name)
        .order_by(func.count(Task.id).desc())
        .limit(1)
    ).all()
    print("лидер:", rows[0])

лидер: ('Анна', 3)

Кортеж «Анна, 3» — готовая строка лидерборда: имя и число задач без единого объекта Task в Python. Хоть топ-пять — поменяй число в limit. Направление сортировки здесь тоже важно: order_by(func.count(Task.id)) по возрастанию дал бы «самого свободного», что для задачки «кого загрузить» тоже полезный запрос.

Общий принцип кочует из запроса в запрос: порядок и лимит — последние штрихи любого select, и они комбинируются со всем, что ты уже знаешь. Where, join, группировка, агрегаты — всё это режет и перекладывает строки до того, как сработают order_by и limit, поэтому финальная выдача всегда отфильтрована, соединена и упорядочена одновременно. Единственное правило — сохранять порядок методов в цепочке: select, потом where и join, потом group_by, и только в конце сортировка с лимитом.

offset: страницы

Постраничный вывод — это тот же sorted список, разрезанный на куски: страница 2 при размере 3 — «пропусти три, дай три». Пропуск начала делает offset.

from sqlalchemy import create_engine, String, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase):
    pass

class Task(Base):
    __tablename__ = "tasks"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(80))

engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)

data = ["автотесты", "багрепорт", "верстка", "документация", "отчет", "презентация"]
with Session(engine) as s:
    s.add_all([Task(title=t) for t in data])
    s.commit()

    titles = [t.title for t in s.scalars(
        select(Task).order_by(Task.title).offset(3).limit(3)
    )]
    print(titles)

['документация', 'отчет', 'презентация']

from sqlalchemy import create_engine, String, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase):
    pass

class Task(Base):
    __tablename__ = "tasks"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(80))

engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)

data = ["автотесты", "багрепорт", "верстка", "документация", "отчет", "презентация"]
with Session(engine) as s:
    s.add_all([Task(title=t) for t in data])
    s.commit()

    page_size = 3
    for page in range(2):
        rows = s.scalars(
            select(Task)
            .order_by(Task.title)
            .offset(page * page_size)
            .limit(page_size)
        ).all()
        print(f"страница {page + 1}:", [t.title for t in rows])

страница 1: ['автотесты', 'багрепорт', 'верстка']
страница 2: ['документация', 'отчет', 'презентация']

Формула страницы: смещение — это номер страницы, умноженный на размер, — одинакова для любой базы и любого ORM. На маленьких данных offset-пагинация работает отлично; на глубоких страницах она дорожает, потому что базе приходится прочитать и выбросить все пропущенные строки: сотая страница по сто записей — это десять тысяч прочитанных ради ста отобранных. Для учебных и даже большинства рабочих объёмов этого хватает; большие ленты переходят на курсорную пагинацию, где следующая страница ищется по «последнему увиденному значению».

Ещё одно требование к страницам, о котором вспоминают поздно: одна и та же сортировка на всех страницах, обязательно с уникальным запасным ключом. Страницы, отсортированные по-разному или без тай-брейера, пересекаются и теряют строки: запись уехала с третьей страницы на вторую между кликами, и пользователь увидит её дважды или не увидит вовсе. Фиксируется это одним и тем же order_by в каждом запросе страницы — держи его в одной функции вместе с offset-арифметикой, а не копируй по местам.

SELECT title, points
FROM tasks
ORDER BY points DESC
LIMIT 3 OFFSET 0;

-- вторая страница того же списка:
SELECT title, points
FROM tasks
ORDER BY title
LIMIT 3 OFFSET 3;

Все четыре строки таблицы — запросы, которые ты уже умеешь собирать: новые методы нарастили возможности, но не усложнили форму. Когда цепочка select-where-order_by-limit становится привычной, половина интерфейсных задач решается одной строкой.

Что дальше

Запросы обрели форму и размер: порядок задаётся явно и с запасным ключом, топы собираются двумя методами, страницы нарезаются offset-ом, а рейтинги строятся прямо по агрегатам. Базовый арсенал ORM полон: модели, сессии, выборки, условия, изменения, связи, join, агрегации, сортировки — на этом можно писать большинство приложений, и это не преувеличение: посмотри на любой crud-сервис, и увидишь запросы из этих девяти уроков.

Дальше начинается расширение границ. Что делать, когда SQL уже написан кем-то другим и его надо исполнить как есть? Когда нужна оконная функция, у которой нет аналога в ORM-выражениях, или экзотическая фича конкретной СУБД? На этот случай в SQLAlchemy есть честная лазейка — сырой SQL через text(): настоящий SQL-текст с параметрами, который исполняется внутри обычной сессии. Урок 14 покажет, как вписывать его в ORM-код, не теряя сессии, параметров и защиты от инъекций.

Порядок — не свойство базы, а часть запроса: order_by задаёт смысл, limit — размер, offset — точку входа. Убери первый — и второй начнёт врать.