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

Есть запрос в potresql WITH RECURSIVE ParentPaths AS ( SELECT Id, array[Id] AS…

  1. 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?

  2. 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(), для выполнения нужных опрераций над данными.

  3. 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.

  4. 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() мы явно указываем таблицу, с которой должно быть выполнено присоединение.

  5. User

    sqlalchemy.exc.ArgumentError: Expected mapped entity or selectable/table as join target

  6. 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().

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