Есть запрос в potresql WITH RECURSIVE ParentPaths AS ( SELECT Id, array[Id] AS…
- User
Есть запрос в potresql
WITH RECURSIVE ParentPaths AS ( SELECT Id, array[Id] AS ParentPath FROM topics WHERE parent = 0 UNION ALL SELECT t.Id, p.ParentPath || t.Id FROM topics t JOIN ParentPaths p ON p.Id = t.parent ) SELECT tasks.id, tasks.task, tasks.answer, tasks.level, array_agg(DISTINCT num) AS ParentPath FROM ParentPaths JOIN tasks_topics ON tasks_topics.topics_id = ParentPaths.Id JOIN tasks ON tasks.id = tasks_topics.task_id
LEFT JOIN LATERAL unnest(ParentPaths.ParentPath) AS num ON true GROUP BY tasks.id;
И есть классы во flask
связующая таблица топики задачи
tasks_topics= db.Table('tasks_topics', db.Column('task_id', db.Integer, db.ForeignKey('tasks.id')), db.Column('topics_id', db.Integer, db.ForeignKey('topics.id')) )
class Tasks(db.Model): tablename = 'tasks' searchable = ['task','id'] # these fields will be indexed by whoosh
id = db.Column(db.Integer, primary_key=True, autoincrement=True) url = db.Column(db.String(120), comment="ЧПУ задачи в адресе (транслитом)") task = db.Column(db.Text, comment='Условие задачи') answer = db.Column(db.Text, comment='Ответ') status = db.Column(db.Boolean, default=0, comment='Статус: опубликована/неактивна (по умолчанию нет)') created_t = db.Column(db.DateTime, comment='Дата и время создания') level = db.Column(db.SmallInteger, default=1, comment='Уровень сложности: 1,2 ... (по умолчанию 1 уровень)') # связи topics = db.relationship('Topics', secondary=tasks_topics, back_populates="tasks")class Topics(db.Model): tablename = 'topics' searchable = ['name']
id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(140), comment="Уникальное название темы(подтемы)") parent = db.Column(db.Integer, default=0, comment="ID родительской темы(если есть)") # связи tasks = db.relationship('Tasks', secondary=tasks_topics, back_populates="topics")Как переписать запрос в sql alchemy?
- Assistant
Вы можете переписать запрос с использованием SQLAlchemy следующим образом:
from sqlalchemy import func ParentPaths = db.aliased(Topics) subquery = ( db.session.query(Topics.id, func.array([Topics.id]).label("ParentPath")) .filter(Topics.parent == 0) .cte(recursive=True) ) ParentPaths = subquery.alias() subquery = ( subquery.union_all( db.session.query(Topics.id, (ParentPaths.c.ParentPath + [Topics.id]).label("ParentPath")) .join(ParentPaths, Topics.parent == ParentPaths.c.id) ) ) query = ( db.session.query( Tasks.id, Tasks.task, Tasks.answer, Tasks.level, func.array_agg(distinct(ParentPaths.c.ParentPath)).label("ParentPath"), ) .join(tasks_topics) .join(ParentPaths, tasks_topics.c.topics_id == ParentPaths.c.id) .join(Tasks, tasks_topics.c.task_id == Tasks.id) .outerjoin( db.func.unnest(ParentPaths.c.ParentPath).label("num"), db.true(), ) .group_by(Tasks.id) ) result = query.all()Здесь мы используем SQLAlchemy для создания подзапроса
subquery, который выполняет рекурсивное объединение. Затем мы создаем основной запрос, который объединяет таблицы и выполняет необходимые присоединения. В результате получаем список, содержащий результаты запроса. Обратите внимание на использованиеdb.aliased()для создания псевдонима таблицыParentPaths, а также на использование функций SQLAlchemy, таких какfunc.array_agg()иdb.func.unnest(), для выполнения нужных опрераций над данными. - User
sqlalchemy.exc.InvalidRequestError: Can't determine which FROM clause to join from, there are multiple FROMS which can join to this entity. Please use the .select_from() method to establish an explicit left side, as well as providing an explicit ON clause if not present already to help resolve the ambiguity.
- Assistant
Извините за путаницу. Чтобы решить ошибку
InvalidRequestError, вам нужно использовать метод.select_from()для явного указания таблицы, с которой должно быть выполнено присоединение, и добавить явное условиеON, если оно еще не присутствует.Вот исправленный код:
from sqlalchemy import func ParentPaths = db.aliased(Topics) tasks_topics_alias = db.aliased(tasks_topics) subquery = ( db.session.query(Topics.id, func.array([Topics.id]).label("ParentPath")) .filter(Topics.parent == 0) .cte(recursive=True) ) ParentPaths = subquery.alias() subquery = ( subquery.union_all( db.session.query(Topics.id, (ParentPaths.c.ParentPath + [Topics.id]).label("ParentPath")) .join(ParentPaths, Topics.parent == ParentPaths.c.id) ) ) query = ( db.session.query( Tasks.id, Tasks.task, Tasks.answer, Tasks.level, func.array_agg(func.distinct(ParentPaths.c.ParentPath)).label("ParentPath"), ) .select_from(ParentPaths) .join(tasks_topics_alias, tasks_topics_alias.c.topics_id == ParentPaths.c.id) .join(Tasks, tasks_topics_alias.c.task_id == Tasks.id) .outerjoin( db.func.unnest(ParentPaths.c.ParentPath).label("num"), db.true(), ) .group_by(Tasks.id) ) result = query.all()Используем
tasks_topics_aliasдля создания псевдонима таблицыtasks_topics. Затем с помощью.select_from()мы явно указываем таблицу, с которой должно быть выполнено присоединение. - User
sqlalchemy.exc.ArgumentError: Expected mapped entity or selectable/table as join target
- Assistant
Извините за путаницу. Чтобы исправить ошибку
ArgumentError: Expected mapped entity or selectable/table as join target, вам нужно явно указать таблицу, с которой выполняется присоединение, в методахjoin()иouterjoin().Вот исправленный код:
from sqlalchemy import func ParentPaths = db.aliased(Topics) tasks_topics_alias = db.aliased(tasks_topics) subquery = ( db.session.query(Topics.id, func.array([Topics.id]).label("ParentPath")) .filter(Topics.parent == 0) .cte(recursive=True) ) ParentPaths = subquery.alias() subquery = ( subquery.union_all( db.session.query(Topics.id, (ParentPaths.c.ParentPath + [Topics.id]).label("ParentPath")) .join(ParentPaths, Topics.parent == ParentPaths.c.id) ) ) query = ( db.session.query( Tasks.id, Tasks.task, Tasks.answer, Tasks.level, func.array_agg(func.distinct(ParentPaths.c.ParentPath)).label("ParentPath"), ) .select_from(ParentPaths) .join(tasks_topics_alias, tasks_topics_alias.c.topics_id == ParentPaths.c.id) .join(Tasks, Tasks.id == tasks_topics_alias.c.task_id) .outerjoin( func.unnest(ParentPaths.c.ParentPath).label("num"), db.true(), ) .group_by(Tasks.id) ) result = query.all()Используем
tasks_topics_aliasдля создания псевдонима таблицыtasks_topics. Затем явно указываем таблицуTasksв методеjoin(). Также явно указываем таблицуfunc.unnest(ParentPaths.c.ParentPath)в методеouterjoin().
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.