Этот ответ исходит от Майка Байера из группы Google sqlalchemy. Я размещаю его здесь, чтобы помочь людям: TLDR: я использовал version 1 ответа Майка, чтобы решить свою проблему, потому что в этом случае у меня нет внешних ключей, участвующих в этой связи, и поэтому я не могу использовать LATERAL. Версия 1 работала отлично, но обязательно обратите внимание на эффект offset. Это сбило меня с толку во время тестирования на некоторое время, потому что я не заметил, что для него было установлено что-то отличное от 0.
Блок кода для версии 1:
subq = s.query(Messages.date).\
filter(Messages.user_id == User.id).\
order_by(Messages.date.desc()).\
limit(1).offset(10).correlate(User).as_scalar()
q = s.query(User).join(
Messages,
and_(User.id == Messages.user_id, Messages.date > subq)
).options(contains_eager(User.messages))
Ответ Майка, поэтому вам следует игнорировать, использует ли он «декларативный», который не имеет ничего общего с запросами, и фактически сначала игнорируйте и Query, потому что в первую очередь это проблема SQL. Вам нужен один оператор SQL, который делает это. Какой запрос в SQL будет загружать множество строк из первичной таблицы, соединенных с первыми десятью строками вторичной таблицы для каждой первичной?
LIMIT сложна, потому что на самом деле она не является частью обычного расчета «реляционной алгебры». Это вне этого, потому что это искусственное ограничение на количество строк. Например, моя первая мысль о том, как это сделать, была неправильной:
select * from users left outer join (select * from messages limit 10) as anon_1 on users.id = anon_1.user_id
Это неправильно, потому что он получает только первые десять сообщений в совокупности, без учета пользователя. Мы хотим получить первые десять сообщений для каждого пользователя, а значит, нам нужно сделать это «выбрать из лимита сообщений 10» индивидуально для каждого пользователя. То есть надо как-то соотносить. Хотя коррелированный подзапрос обычно не разрешен как элемент FROM и разрешен только как выражение SQL, он может возвращать только один столбец и одну строку; мы обычно не можем ПРИСОЕДИНИТЬСЯ к коррелированному подзапросу в простом ванильном SQL. Однако мы можем сопоставить предложение ON в JOIN, чтобы сделать это возможным в ванильном SQL.
Но сначала, если мы работаем с современной версией Postgresql, мы можем нарушить это обычное правило корреляции и использовать ключевое слово LATERAL, которое разрешает корреляцию в предложении FROM. LATERAL поддерживается только современными версиями Postgresql, и это упрощает задачу:
select * from users left outer join lateral
(select * from message where message.user_id = users.id order by messages.date desc limit 10) as anon1 on users.id = anon_1.user_id
мы поддерживаем ключевое слово LATERAL. Вышеприведенный запрос выглядит так:
subq = s.query(Messages).\
filter(Messages.user_id == User.id).\
order_by(Messages.date.desc()).limit(10).subquery().lateral()
q = s.query(User).outerjoin(subq).\
options(contains_eager(User.messages, alias=subq))
Обратите внимание, что выше, чтобы ВЫБРАТЬ пользователей и сообщения и создать их в коллекции User.messages, необходимо использовать параметр «contains_eager()», и для этого «динамический» должен исчезнуть. Это не единственный вариант, вы можете, например, построить второе отношение для User.messages, которое не имеет «динамического», или вы можете просто загрузить из запроса (пользователь, сообщение) отдельно и организовать кортежи результатов по мере необходимости.
если вы не используете Postgresql или версию Postgresql, которая не поддерживает LATERAL, вместо этого корреляция должна быть включена в предложение ON соединения. SQL выглядит так:
select * from users left outer join messages on
users.id = messages.user_id and messages.date > (select date from messages where messages.user_id = users.id order by date desc limit 1 offset 10)
Здесь, чтобы втиснуть туда LIMIT, мы на самом деле проходим первые 10 строк с помощью OFFSET, а затем выполняем LIMIT 1, чтобы получить дату, представляющую нижнюю границу даты, которую мы хотим для каждого пользователя. Затем мы должны объединиться при сравнении этой даты, что может быть дорого, если этот столбец не проиндексирован, а также может быть неточным, если есть повторяющиеся даты.
Этот запрос выглядит так:
subq = s.query(Messages.date).\
filter(Messages.user_id == User.id).\
order_by(Messages.date.desc()).\
limit(1).offset(10).correlate(User).as_scalar()
q = s.query(User).join(
Messages,
and_(User.id == Messages.user_id, Messages.date >= subq)
).options(contains_eager(User.messages))
Я не доверяю такого рода запросам без хорошего теста, поэтому приведенный ниже POC включает обе версии, включая проверку работоспособности.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
import datetime
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
messages = relationship(
'Messages', order_by='desc(Messages.date)')
class Messages(Base):
__tablename__ = 'message'
id = Column(Integer, primary_key=True)
user_id = Column(ForeignKey('user.id'))
date = Column(Date)
e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
s = Session(e)
s.add_all([
User(id=i, messages=[
Messages(id=(i * 20) + j, date=datetime.date(2017, 3, j))
for j in range(1, 20)
]) for i in range(1, 51)
])
s.commit()
top_ten_dates = set(datetime.date(2017, 3, j) for j in range(10, 20))
def run_test(q):
all_u = q.all()
assert len(all_u) == 50
for u in all_u:
messages = u.messages
assert len(messages) == 10
for m in messages:
assert m.user_id == u.id
received = set(m.date for m in messages)
assert received == top_ten_dates
# version 1. no LATERAL
s.close()
subq = s.query(Messages.date).\
filter(Messages.user_id == User.id).\
order_by(Messages.date.desc()).\
limit(1).offset(10).correlate(User).as_scalar()
q = s.query(User).join(
Messages,
and_(User.id == Messages.user_id, Messages.date > subq)
).options(contains_eager(User.messages))
run_test(q)
# version 2. LATERAL
s.close()
subq = s.query(Messages).\
filter(Messages.user_id == User.id).\
order_by(Messages.date.desc()).limit(10).subquery().lateral()
q = s.query(User).outerjoin(subq).\
options(contains_eager(User.messages, alias=subq))
run_test(q)
person
melchoir55
schedule
02.05.2017
