Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 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, необходимо использовать методы 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 используется для изменения имени полей в выборке.

    Помните, что вам может потребоваться внести изменения в код в зависимости от структуры вашей базы данных и требований вашего приложения.

  3. User

    NameError: name 'array' is not defined

  4. Assistant

    Моя ошибка, извините за путаницу. Вместо функции array() нужно использовать функцию db.array(). Вот исправленные строки:

    db.func.array([Topics.id]).label('ParentPath')
    

    и

    db.func.array_agg(db.distinct(ParentPaths_alias.c.ParentPath)).label('ParentPath')
    

    Пожалуйста, попробуйте использовать исправленный код. Извините за предыдущую путаницу!

  5. 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.
    
  6. 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.

    Пожалуйста, попробуйте этот код и сообщите, работает ли он корректно. Извините за предыдущие проблемы!

  7. User

    sqlalchemy.exc.InvalidRequestError: Query.select_from() being called on a Query with existing criterion.

  8. 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(). Пожалуйста, попробуйте этот код и сообщите, работает ли он корректно. Извините за предыдущие проблемы!

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