Skip to content
The Chat Archive
gpt-3.5-turbo-0613· OpenAI WildChat-4.8M (AI2)

Есть запрос в potresql WITH RECURSIVE topicparent(id, path) AS (SELECT id, ARRAY[id]…

  1. User

    Есть запрос в potresql

    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 topics.name) AS topic_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 GROUP BY limited_tasks.id, limited_tasks.task, limited_tasks.answer ORDER BY limited_tasks.id ASC

    И есть классы во 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?

  2. Assistant

    Чтобы переписать данный запрос на SQLAlchemy, вам нужно создать соответствующие модели и использовать методы join(), group_by() и order_by() для объединения таблиц и применения операций GROUP BY и ORDER BY.

    Вот переписанный запрос на SQLAlchemy:

    from sqlalchemy import func
    
    # Связующая таблица топики задачи
    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']
    
        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")
    
    # Переписанный запрос
    limited_tasks = db.session.query(Tasks).order_by(Tasks.id.asc()).limit(100).subquery()
    topic_parent = db.session.query(Topics.id, func.array([Topics.id])).filter(Topics.parent == 0).cte(recursive=True)
    topic_parent_alias = topic_parent.alias()
    limited_tasks_alias = aliased(limited_tasks)
    cte2 = topic_parent.union_all(db.session.query(Topics.id, topic_parent_alias.c.path.op('||')(Topics.id)).join(topic_parent_alias, topic_parent_alias.c.id == Topics.parent))
    query = db.session.query(limited_tasks_alias.id, limited_tasks_alias.task, limited_tasks_alias.answer, func.array_agg(Topics.name.distinct()).label('topic_name')).join(tasks_topics).join(cte2, cte2.c.id == tasks_topics.c.topics_id).join(Topics, Topics.id == cte2.c.linked_id).group_by(limited_tasks_alias.id, limited_tasks_alias.task, limited_tasks_alias.answer).order_by(limited_tasks_alias.id.asc())
    

    Обратите внимание, что вам может потребоваться дополнительно импортировать некоторые функции и операторы SQLAlchemy, такие как aliased(), join() и func.array().

  3. User

    raise exception AttributeError: path

  4. Assistant

    Измените выражение cte2 следующим образом:

    cte2 = topic_parent.union_all(db.session.query(Topics.id, func.array_cat(topic_parent_alias.c.path, [Topics.id])).join(topic_parent_alias, topic_parent_alias.c.id == Topics.parent))
    

    Вместо оператора || мы используем функцию array_cat(), чтобы объединить массивы path и [Topics.id].

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.