Как указать таблицы FROM в подзапросах SQLAlchemy?

Я пытаюсь получить в одном запросе фиксированный набор строк, а также некоторые другие строки, найденные подзапросом. Моя проблема в том, что запрос, сгенерированный кодом SQLAlchemy, неверен.

Проблема заключается в том, что запрос, сгенерированный SQLAlchemy, выглядит следующим образом:

SELECT tbl.id AS tbl_id FROM tbl WHERE tbl.id IN ( SELECT t2.id AS t2_id FROM tbl AS t2, tbl AS t1 WHERE t2.id = ( SELECT t3.id AS t3_id FROM tbl AS t3, tbl AS t1 WHERE t3.id < t1.id ORDER BY t3.id DESC LIMIT 1 OFFSET 0 ) AND t1.id IN (4, 8) ) OR tbl.id IN (0, 8) 

в то время как правильный запрос не должен иметь второй tbl AS t1 (целью этого запроса является выбор идентификаторов 0 и 8, а также идентификаторы непосредственно перед 4 и 8).

К сожалению, я не могу найти, как заставить SQLAlchemy генерировать правильный (см. Код ниже).

Также желательно приветствовать предложения с тем же результатом с более простым запросом (они должны быть эффективными, хотя – я пробовал несколько вариантов, а некоторые из них были намного медленнее в моем реальном варианте использования).

Код, создающий запрос:

 from sqlalchemy import create_engine, or_ from sqlalchemy import Column, Integer, MetaData, Table from sqlalchemy.orm import sessionmaker engine = create_engine('sqlite:///:memory:', echo=True) meta = MetaData(bind=engine) table = Table('tbl', meta, Column('id', Integer)) session = sessionmaker(bind=engine)() meta.create_all() # Insert IDs 0, 2, 4, 6, 8. i = table.insert() i.execute(*[dict(id=i) for i in range(0, 10, 2)]) print session.query(table).all() # output: [(0,), (2,), (4,), (6,), (8,)] # Subquery of interest: look for the row just before IDs 4 and 8. sub_query_txt = ( 'SELECT t2.id ' 'FROM tbl t1, tbl t2 ' 'WHERE t2.id = ( ' ' SELECT t3.id from tbl t3 ' ' WHERE t3.id < t1.id ' ' ORDER BY t3.id DESC ' ' LIMIT 1) ' 'AND t1.id IN (4, 8)') print session.execute(sub_query_txt).fetchall() # output: [(2,), (6,)] # Full query of interest: get the rows mentioned above, as well as more rows. query_txt = ( 'SELECT * ' 'FROM tbl ' 'WHERE ( ' ' id IN (%s) ' 'OR id IN (0, 8))' ) % sub_query_txt print session.execute(query_txt).fetchall() # output: [(0,), (2,), (6,), (8,)] # Attempt at an SQLAlchemy translation (from innermost sub-query to full query). t1 = table.alias('t1') t2 = table.alias('t2') t3 = table.alias('t3') q1 = session.query(t3.c.id).filter(t3.c.id < t1.c.id).order_by(t3.c.id.desc()).\ limit(1) q2 = session.query(t2.c.id).filter(t2.c.id == q1, t1.c.id.in_([4, 8])) q3 = session.query(table).filter( or_(table.c.id.in_(q2), table.c.id.in_([0, 8]))) print list(q3) # output: [(0,), (6,), (8,)] 

То, что вам не хватает, – это корреляция между внутренним подзапросом и следующим уровнем; без корреляции, SQLAlchemy будет включать в себя псевдоним t1 во внутреннем подзапросе:

 >>> print str(q1) SELECT t3.id AS t3_id FROM tbl AS t3, tbl AS t1 WHERE t3.id < t1.id ORDER BY t3.id DESC LIMIT ? OFFSET ? >>> print str(q1.correlate(t1)) SELECT t3.id AS t3_id FROM tbl AS t3 WHERE t3.id < t1.id ORDER BY t3.id DESC LIMIT ? OFFSET ? 

Обратите внимание, что tbl AS t1 теперь отсутствует в запросе. Из документации метода .correlate() :

Верните конструкцию запроса, которая будет сопоставлять заданные предложения FROM с предложением Query или select ().

Таким образом, t1 считается частью прилагаемого запроса и не указан в самом запросе.

Теперь ваш запрос работает:

 >>> q1 = session.query(t3.c.id).filter(t3.c.id < t1.c.id).order_by(t3.c.id.desc()).\ ... limit(1).correlate(t1) >>> q2 = session.query(t2.c.id).filter(t2.c.id == q1, t1.c.id.in_([4, 8])) >>> q3 = session.query(table).filter( ... or_(table.c.id.in_(q2), table.c.id.in_([0, 8]))) >>> print list(q3) 2012-10-24 22:16:22,239 INFO sqlalchemy.engine.base.Engine SELECT tbl.id AS tbl_id FROM tbl WHERE tbl.id IN (SELECT t2.id AS t2_id FROM tbl AS t2, tbl AS t1 WHERE t2.id = (SELECT t3.id AS t3_id FROM tbl AS t3 WHERE t3.id < t1.id ORDER BY t3.id DESC LIMIT ? OFFSET ?) AND t1.id IN (?, ?)) OR tbl.id IN (?, ?) 2012-10-24 22:16:22,239 INFO sqlalchemy.engine.base.Engine (1, 0, 4, 8, 0, 8) [(0,), (2,), (6,), (8,)] 

Я только точно понимаю, что я понимаю запрос, о котором вы просите. Давайте сломаем его, хотя:

целью этого запроса является выбор идентификаторов 0 и 8, а также идентификаторы перед 4 и 8.

Похоже, вы хотите запросить два вида вещей, а затем объединить их. Правильный оператор для этого – union . Сделайте простые запросы и добавьте их в конце. Я начну со второго бита: «ids перед X».

Начать с; давайте посмотрим на все идентификаторы, которые до определенного значения. Для этого мы присоединяемся к таблице непосредственно с помощью < :

 # select t1.id t1_id, t2.id t2_id from tbl t1 join tbl t2 on t1.id < t2.id; t1_id | t2_id -------+------- 0 | 2 0 | 4 0 | 6 0 | 8 2 | 4 2 | 6 2 | 8 4 | 6 4 | 8 6 | 8 (10 rows) 

Это, безусловно, дает нам все пары строк, где левое меньше правого. Из всех них мы хотим, чтобы строки для данного t2_id были как можно выше; Группируем t2_id и выбираем максимум t1_id

 # select max(t1.id), t2.id from tbl t1 join tbl t2 on t1.id < t2.id group by t2.id; max | id -----+------- 0 | 2 2 | 4 4 | 6 6 | 8 (4 rows) 

Ваш запрос, используя limit , мог бы достичь этого, но обычно это хорошая идея, чтобы избежать использования этого метода, когда существуют альтернативы, потому что у раздела нет хорошей переносимой поддержки в реализациях базы данных. Sqlite может использовать эту технику, но postgresql ей не нравится, она использует технику под названием «аналитические запросы» (которые являются стандартизованными и более общими). MySQL не может этого сделать. Вышеприведенный запрос работает последовательно во всех СУБД.

остальная часть работы просто используется in или в других эквивалентных фильтровальных запросах, и их нетрудно выразить в sqlalchemy. Планировщик …

 >>> import sqlalchemy as sa >>> from sqlalchemy.orm import Query >>> engine = sa.create_engine('sqlite:///:memory:') >>> meta = sa.MetaData(bind=engine) >>> table = sa.Table('tbl', meta, sa.Column('id', sa.Integer)) >>> meta.create_all() >>> table.insert().execute([{'id':i} for i in range(0, 10, 2)]) >>> t1 = table.alias() >>> t2 = table.alias() >>> before_filter = [4, 8] 

Первый интересный бит – это выражение «max (id)». это необходимо, чтобы мы могли ссылаться на него более одного раза и вытащить его из подзапроса.

 >>> c1 = sa.func.max(t1.c.id).label('max_id') >>> # ^^^^^^ 

Часть «тяжелого подъема» запроса присоединяется к вышеуказанным псевдонимам, группе и выбирает максимальную

 >>> q1 = Query([c1, t2.c.id]) \ ... .join((t2, t1.c.id < t2.c.id)) \ ... .group_by(t2.c.id) \ ... .filter(t2.c.id.in_(before_filter)) 

Поскольку мы будем использовать объединение, нам нужно это, чтобы создать правильное количество полей: мы завершаем его в подзапрос и проект вплоть до единственного столбца, который нас интересует. Это будет имя, которое мы дали ему в приведенном выше label() .

 >>> q2 = Query(q1.subquery().c.max_id) >>> # ^^^^^^ 

Другая половина союза намного проще:

 >>> t3 = table.alias() >>> exact_filter = [0, 8] >>> q3 = Query(t3).filter(t3.c.id.in_(exact_filter)) 

Осталось только объединить их:

 >>> q4 = q2.union(q3) >>> engine.execute(q4.statement).fetchall() [(0,), (2,), (6,), (8,)]