Есть запрос в 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, необходимо использовать методы query и join. Вот как можно переписать данный запрос:
ParentPaths = db.session \ .query(Topics.id.label('Id'), array([Topics.id]).label('ParentPath')) \ .filter(Topics.parent == 0) \ .cte('ParentPaths', recursive=True) ParentPaths_alias = db.aliased(ParentPaths, name='p') Topics_alias = db.aliased(Topics, name='t') query = db.session \ .query( Tasks.id, Tasks.task, Tasks.answer, Tasks.level, db.func.array_agg(db.distinct(ParentPaths_alias.c.ParentPath)).label('ParentPath') ) \ .join(tasks_topics) \ .join(ParentPaths_alias, ParentPaths_alias.c.Id == tasks_topics.c.topics_id) \ .join(Tasks, Tasks.id == tasks_topics.c.task_id) \ .outerjoin( db.func.unnest(ParentPaths_alias.c.ParentPath).alias('ParentPath'), db.true() ) \ .group_by(Tasks.id)Примечание:
db.session.queryиспользуется для создания запроса.db.session.query(Topics.id.label('Id'), array([Topics.id]).label('ParentPath'))выбирает поля из модели Topics и создает псевдонимы полей.db.session.query(...).cte(...)создает Common Table Expression (CTE) для рекурсивного запроса.db.aliased(Topics, name='t')создает псевдоним для таблицы Topics.db.func.array_aggиdb.func.distinctиспользуются для агрегации исключительных значений массивов ParentPath.db.join,db.outerjoinиdb.func.unnestиспользуются для объединения таблиц и развертывания массивов..labelиспользуется для изменения имени полей в выборке.
Помните, что вам может потребоваться внести изменения в код в зависимости от структуры вашей базы данных и требований вашего приложения.
- User
NameError: name 'array' is not defined
- Assistant
Моя ошибка, извините за путаницу. Вместо функции
array()нужно использовать функциюdb.array(). Вот исправленные строки:db.func.array([Topics.id]).label('ParentPath')и
db.func.array_agg(db.distinct(ParentPaths_alias.c.ParentPath)).label('ParentPath')Пожалуйста, попробуйте использовать исправленный код. Извините за предыдущую путаницу!
- User
ParentPaths = db.session.query(Topics.id.label('Id'), db.func.array([Topics.id]).label('ParentPath')).filter(Topics.parent == 0).cte('ParentPaths', recursive=True)
ParentPaths_alias = db.aliased(ParentPaths, name='p') Topics_alias = db.aliased(Topics, name='t') query = db.session.query( Tasks.id, Tasks.task, Tasks.answer, Tasks.level, db.func.array_agg(db.distinct(ParentPaths_alias.c.ParentPath)).label('ParentPath') ).join(tasks_topics).join(ParentPaths_alias, ParentPaths_alias.c.Id == tasks_topics.c.topics_id).join(Tasks, Tasks.id == tasks_topics.c.task_id).outerjoin( db.func.unnest(ParentPaths_alias.c.ParentPath).alias('ParentPath'), db.true() ).group_by(Tasks.id) print(query.all()) 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
Извините за путаницу в предложенном коде. Для устранения данной ошибки потребуется задать явное соединение и использовать метод
select_from():ParentPaths = db.session.query(Topics.id.label('Id'), db.func.array([Topics.id]).label('ParentPath')).filter(Topics.parent == 0).cte('ParentPaths', recursive=True) ParentPaths_alias = db.alias(ParentPaths, name='p') Topics_alias = db.alias(Topics, name='t') query = db.session.query( Tasks.id, Tasks.task, Tasks.answer, Tasks.level, db.func.array_agg(db.distinct(ParentPaths_alias.c.ParentPath)).label('ParentPath') ).join(tasks_topics).join(ParentPaths_alias, ParentPaths_alias.c.Id == tasks_topics.c.topics_id).join(Tasks, Tasks.id == tasks_topics.c.task_id).outerjoin( db.func.unnest(ParentPaths_alias.c.ParentPath).alias('ParentPath'), db.true() ).group_by(Tasks.id).select_from(Tasks) print(query.all())В этом обновленном коде использован метод
db.alias()для создания псевдонимов таблиц. А также добавлен методselect_from()для указания таблицы основного запроса вquery.Пожалуйста, попробуйте этот код и сообщите, работает ли он корректно. Извините за предыдущие проблемы!
- User
sqlalchemy.exc.InvalidRequestError: Query.select_from() being called on a Query with existing criterion.
- Assistant
Прошу прощения за путаницу. Для убраня этой ошибки можно изменить порядок объединения таблиц в запросе и использовать
join()метод послеselect_from(). Вот исправленный код:ParentPaths = db.session.query(Topics.id.label('Id'), db.func.array([Topics.id]).label('ParentPath')).filter(Topics.parent == 0).cte('ParentPaths', recursive=True) ParentPaths_alias = db.alias(ParentPaths, name='p') Topics_alias = db.alias(Topics, name='t') query = db.session.query( Tasks.id, Tasks.task, Tasks.answer, Tasks.level, db.func.array_agg(db.distinct(ParentPaths_alias.c.ParentPath)).label('ParentPath') ).select_from(Tasks).join(tasks_topics).join(ParentPaths_alias, ParentPaths_alias.c.Id == tasks_topics.c.topics_id).join(Tasks, Tasks.id == tasks_topics.c.task_id).outerjoin( db.func.unnest(ParentPaths_alias.c.ParentPath).alias('ParentPath'), db.true() ).group_by(Tasks.id) print(query.all())Теперь метод
select_from()вызывается до методаjoin(). Пожалуйста, попробуйте этот код и сообщите, работает ли он корректно. Извините за предыдущие проблемы!
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.