Метод correlate класса Select

Метод correlate класса Select управляет корреляцией подзапросов. Корреляция означает, что подзапрос ссылается на таблицы из внешнего запроса, а не включает их в свой собственный FROM. Метод принимает одну или несколько сущностей (таблиц или моделей), которые следует исключить из FROM подзапроса и связать с внешним запросом. Если аргументы не переданы, коррелируются все таблицы, которые могут быть связаны с внешним запросом.

Синтаксис

select(...).correlate(*entities)

Пример

Давайте создадим модель Article и построим подзапрос, который коррелирует с внешним запросом по таблице articles. Без корреляции подзапрос попытался бы включить articles в свой FROM, что привело бы к декартову произведению. С корреляцией подзапрос ссылается на внешнюю таблицу:

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

class Base(DeclarativeBase):
	pass

class Article(Base):
	__tablename__ = 'articles'
	
	id: Mapped[int] = mapped_column(primary_key=True)
	title: Mapped[str]
	text: Mapped[str]
	status: Mapped[str]
	num: Mapped[int]

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

with Session(engine) as session:
	session.add_all([
		Article(title='article 1', text='text 1', status='published', num=1),
		Article(title='article 2', text='text 2', status='draft', num=2),
	])
	session.commit()
	
	subq = (
		select(func.count(Article.id))
		.where(Article.status == 'published')
		.correlate(Article)
		.scalar_subquery()
	)
	
	stmt = select(Article.title, subq.label('published_count'))
	res = session.execute(stmt).all()
	print(res)

Результат выполнения кода:

[('article 1', 1), ('article 2', 1)]

Итоговый SQL запрос:

SELECT articles.title, (SELECT count(articles.id) AS count_1
FROM articles
WHERE articles.status = ?) AS published_count
FROM articles

Пример

Давайте рассмотрим случай с двумя таблицами. Создадим модель User и свяжем ее с Article. Подзапрос будет коррелировать только с articles, но не с users:

from sqlalchemy import create_engine, select, func, ForeignKey
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]

class Article(Base):
	__tablename__ = 'articles'
	
	id: Mapped[int] = mapped_column(primary_key=True)
	title: Mapped[str]
	text: Mapped[str]
	status: Mapped[str]
	num: Mapped[int]
	user_id: Mapped[int] = mapped_column(ForeignKey('users.id'))
	
	user: Mapped[User] = relationship()

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

with Session(engine) as session:
	user = User(name='user')
	session.add(user)
	session.flush()
	
	session.add_all([
		Article(title='article 1', text='text 1', status='published', num=1, user_id=user.id),
		Article(title='article 2', text='text 2', status='draft', num=2, user_id=user.id),
	])
	session.commit()
	
	subq = (
		select(func.count(Article.id))
		.where(Article.user_id == User.id)
		.correlate(User)
		.scalar_subquery()
	)
	
	stmt = select(User.name, subq.label('article_count'))
	res = session.execute(stmt).all()
	print(res)

Результат выполнения кода:

[('user', 2)]

Итоговый SQL запрос:

SELECT users.name, (SELECT count(articles.id) AS count_1
FROM articles
WHERE articles.user_id = users.id) AS article_count
FROM users

Смотрите также

класс Select,
который представляет SELECT-запрос
метод correlate_except,
который коррелирует все таблицы, кроме указанных
метод subquery,
который превращает запрос в подзапрос
метод cte,
который создает обобщенное табличное выражение