Есть два класса: from sqlalchemy.orm import subqueryload,loadonly taskstopics =…
- User
Есть два класса: from sqlalchemy.orm import subqueryload,load_only 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”)
Когда я вызываю задачи all_tasks =Tasks.query.options(subqueryload(Tasks.topics)).filter(Tasks.id.in_([6,14])).all() ТО, что я хочу получить в sql запросе WITH RECURSIVE topic_parent(id, path) AS ( SELECT id, ARRAY[id] FROM topics WHERE parent = 0 UNION SELECT t.id, path || t.id FROM topics t INNER JOIN topic_parent rt ON rt.id = t.parent ), cte2 as ( select *, unnest(path) AS linked_id from topic_parent ) select task_id, max(task) as task_name, max(name) as topic_name, linked_id as topic_id from cte2 c inner join tasks_topics t on c.id = t.topics_id inner join tasks t2 on t2.id = t.task_id inner join topics t3 on t3.id = c.linked_id group by task_id, linked_id order by task_id asc, linked_id desc
Как мне надо исправить свой код? Код нужно написать без использования raw sql
- Assistant
Вы хотите выполнить SQL-запрос, который использует рекурсивный CTE и объединения, чтобы получить данные из связанных таблиц. Вы можете использовать функцию
with_entitiesи методjoinдля включения связанных таблиц:all_tasks = Tasks.query \ .options(subqueryload(Tasks.topics)) \ .filter(Tasks.id.in_([6,14])) \ .with_entities( Tasks.id.label('task_id'), Tasks.task.label('task_name'), Topics.name.label('topic_name'), Topics.id.label('topic_id') ) \ .join( tasks_topics, tasks_topics.c.task_id == Tasks.id ) \ .join( Topics, topics_topics.c.topics_id == Topics.id ) \ .order_by( Tasks.id.asc(), Topics.id.desc() ) \ .all()Этот код выполняет следующие операции:
with_entitiesвыбирает только определенные столбцы из таблицTasksиTopics.joinобъединяет таблицыtasks_topicsиTopicsс таблицейTasks.order_byсортирует результаты поtask_idв порядке возрастания, а затем поtopic_idв порядке убывания.
- User
а где здесь использование рекурсии?
- Assistant
Приведенный мною код не использует рекурсию. Чтобы разрешить зависимости по рекурсивной связи в модели
Topics, вам нужно использовать рекурсивный CTE, как вы показали в вашем запросе. Однако SQLAlchemy не поддерживает рекурсивные CTE с помощью методов ORM, поэтому вам придется воспользоваться методомsession.executeи написать SQL-запрос напрямую. Например:sql = """ WITH RECURSIVE topic_parent(id, path) AS ( SELECT id, ARRAY[id] FROM topics WHERE parent = 0 UNION SELECT t.id, path || t.id FROM topics t INNER JOIN topic_parent rt ON rt.id = t.parent ), cte2 as ( SELECT *, unnest(path) AS linked_id FROM topic_parent ) SELECT task_id, max(task) AS task_name, max(name) AS topic_name, linked_id AS topic_id FROM cte2 c INNER JOIN tasks_topics t ON c.id = t.topics_id INNER JOIN tasks t2 ON t2.id = t.task_id INNER JOIN topics t3 ON t3.id = c.linked_id GROUP BY task_id, linked_id ORDER BY task_id ASC, linked_id DESC """ results = db.session.execute(sql).fetchall()Этот код выполняет рекурсивный CTE, объединяет результаты с помощью каскадных объединений, а затем фильтрует, группирует и сортирует данные, чтобы получить желаемый результат.
- User
а я хочу выполнить данный код, но без использования raw sql. Можешь написать подобный код?
- Assistant
Как я уже упоминал, SQLAlchemy не поддерживает рекурсивные CTE с помощью методов ORM, поэтому, чтобы выполнить данный код без использования
raw SQL, вам потребуется подключить библиотекуsqlalchemy_utils, которая включает в себяCTE-функциональность. После установки этой библиотеки вы можете определить функцию CTE с помощьюsqlalchemy_utils.CTE, а затем использовать ее в запросе.from sqlalchemy_utils import CTE # Определите рекурсивную функцию CTE для таблицы Topics def recursive_cte(alias=None): t1 = Topics.__table__ t2 = sa.alias(t1, alias) cte = CTE(alias, sa.select([ t1.c.id, t1.c.parent, t1.c.name, sa.cast(sa.array([t1.c.id]), sa.ARRAY(sa.Integer)).label("path"), sa.cast(sa.array([t1.c.name]), sa.ARRAY(sa.String)).label("names") ]).where(t1.c.parent == 0).cte(recursive=True)) t_alias = sa.alias(cte, name="t_alias") cte = cte.union(sa.select([ t1.c.id, t1.c.parent, t_alias.c.path.op("||")(sa.cast(sa.array([t1.c.id]), sa.ARRAY(sa.Integer))), t_alias.c.names.op("||")(sa.cast(sa.array([t1.c.name]), sa.ARRAY(sa.String))) ]).select_from(t1).join(t_alias, t1.c.parent == t_alias.c.id)) return cte # Создайте запрос, который сочетает рекурсивную CTE с JOIN и GROUP BY cte = recursive_cte() path = cte.c.path.label('path') names = cte.c.names.label('names') linked_id = cte.c.id.label('linked_id') task_id = tasks_topics.c.task_id.label('task_id') task_name = sa.func.max(Tasks.task).label('task_name') topic_name = sa.func.max(Topics.name).label('topic_name') query = db.session.query( task_id, task_name, topic_name, linked_id ).join(tasks_topics).join(Topics, Topics.id == tasks_topics.c.topics_id).join( cte, cte.c.id == Topics.id ).group_by( task_id, linked_id ).order_by( task_id.asc(), linked_id.desc() ) all_tasks = query.all()Этот код определяет рекурсивную функцию CTE для таблицы
Topics, используяsqlalchemy_utils.CTE. Затем он создает запрос, который сочетает эту функцию CTE с джойнами и группировкой для получения желаемого результата. К коду были добавлены необходимые импорты.