Нужно переписать запрос в sql alchemy WITH RECURSIVE topicparent(id, path) AS (SELECT…
- User
Нужно переписать запрос в sql alchemy 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 ), limited_tasks AS (SELECT * FROM public.tasks ORDER BY id ASC LIMIT 100 ) SELECT limited_tasks.id, limited_tasks.task, limited_tasks.answer, array_agg(DISTINCT classes.name) AS class_name, array_agg(DISTINCT topics.name) AS topic_name, array_agg(DISTINCT authors.name) AS authors_name FROM limited_tasks INNER JOIN tasks_topics ON limited_tasks.id = tasks_topics.task_id INNER JOIN (SELECT *, unnest(path) AS linked_id FROM topic_parent) cte2 ON cte2.id = tasks_topics.topics_id INNER JOIN topics ON topics.id = cte2.linked_id INNER JOIN tasks_classes ON limited_tasks.id = tasks_classes.tasks_id INNER JOIN classes ON classes.id = tasks_classes.classes_id INNER JOIN tasks_authors ON limited_tasks.id = tasks_authors.tasks_id INNER JOIN authors ON authors.id = tasks_authors.authors_id GROUP BY limited_tasks.id, limited_tasks.task, limited_tasks.answer ORDER BY limited_tasks.id ASC
- Assistant
query = session.query(limited_tasks.c.id, limited_tasks.c.task, limited_tasks.c.answer, func.array_agg(distinct(classes.c.name)).label('class_name'), func.array_agg(distinct(topics.c.name)).label('topic_name'), func.array_agg(distinct(authors.c.name)).label('authors_name') ).
select_from(limited_tasks).
join(tasks_topics, limited_tasks.c.id == tasks_topics.c.task_id).
join(select([topics, func.unnest(topic_parent.c.path).label('linked_id')]).
select_from(topic_parent), topic_parent.c.id == tasks_topics.c.topics_id).
join(topics, topics.c.id == topic_parent.c.linked_id).
join(tasks_classes, limited_tasks.c.id == tasks_classes.c.tasks_id).
join(classes, classes.c.id == tasks_classes.c.classes_id).
join(tasks_authors, limited_tasks.c.id == tasks_authors.c.tasks_id).
join(authors, authors.c.id == tasks_authors.c.authors_id).
group_by(limited_tasks.c.id, limited_tasks.c.task, limited_tasks.c.answer).
order_by(limited_tasks.c.id.asc())
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.