CODEWITHSUNDEEP
pythonpostgresqlflask-sqlalchemy
Amish Shabani
Amish Shabani

Reputation: 759

missing FROM-clause entry for table in SQLAlchemy

This is my query builder method:

def query_builder(search_parameter):
    query = BusinessOwner.query
    # query = query.select_from(BusinessOwnerTask)
    query = query.join(services, BusinessOwnerTask.services_id == services.c.id). \
        join(BusinessOwnerTask, BusinessOwnerTask.business_owner_id == BusinessOwner.id)
    query = query.filter(services.c.service_grade_id == search_parameter.service_grade)
    if len(search_parameter.name) > 0:
        query = query.filter(BusinessOwner.name == search_parameter.name)
    if len(search_parameter.service_types) > 0:
        query = query.filter(BusinessOwnerTask.services_id.in_(search_parameter.service_types))

    return query

This code has been run on sqlite3 ,However, when query.all() calls, it complains on postgres:

ProgrammingError: (psycopg2.ProgrammingError) missing FROM-clause entry for table "business_owner_tasks" LINE 2: FROM business_owners JOIN services ON business_owner_tasks.s...

I decided to add this:

query = query.select_from(BusinessOwnerTask)

It complains:

ProgrammingError: (psycopg2.ProgrammingError) table name "business_owner_tasks" specified more than once

What is my mistake?

Upvotes: 2

Views: 5301

Answers (1)

Amish Shabani
Amish Shabani

Reputation: 759

I change query builder to this one:

query = db.session.query(BusinessOwnerTask, BusinessOwner, services) \
            .join(services, BusinessOwnerTask.services_id == services.c.id) \
            .join(BusinessOwner, BusinessOwner.id == BusinessOwnerTask.business_owner_id) \
            .filter(services.c.service_grade_id == search_parameter.service_grade)

        if len(search_parameter.name) > 0:
            query = query.filter(BusinessOwner.name == search_parameter.name)
        if len(search_parameter.service_types) > 0:
            query = query.filter(services.c.service_type_id.in_(search_parameter.service_types))

Tip:

It is better to first make your query on pure sql on pgAdmin or other tools. Then make your query on ORM tool

Upvotes: 2

Related Questions