PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – can't label first table in sqlalchemy in query with join

I have no problem with labeling second table ("jtable"), but how I can make the same with first one ("table")? Because I haven’t any allowed methods to do this.

async def get_join_table(
        db: AsyncSession,
        table: PostSQL | VoteSQL | UserSQL,
        jtable: PostSQL | VoteSQL | UserSQL,
        limit: int = None,
        offset: int = None,
        contains: str = "",
        order=None,
):

    query = (
        select(table, func.count(jtable.post_id).label("votes"))
        .join(jtable, table.id == jtable.post_id, isouter=True)
        .group_by(table.id)
        .limit(limit)
        .offset(offset)
        .filter(PostSQL.title.contains(contains))
        .order_by(order)
    )
    
    response = await db.execute(query)
    posts = response.mappings().all()
    return posts

I would like to rename POSTSQL to POST1 in answer like in this sql script:

select
    posts as POSTS1,
    count(votes.post_id) as VOTE
from
    posts
    left outer join votes ON votes.post_id = posts.id
group by posts.id;

Response from Postgres DB

Answer that I want to modify:

{
    "PostSQL": {
        "content": "GRAND",
        "title": "misery",
        "created_at": "2024-01-27T10:48:35.975442",
        "id": 22,
        "published": true,
        "owner_id": 4,
        "owner": {
            "email": "[email protected]",
            "id": 4,
            "created_at": "2024-01-20T12:44:36.365265"
        }
    },
    "votes": 0
}
Tags:

2

Answers


  1. 0

    You can use .label() for columns not for full tables/models.
    Since func.count(jtable.post_id) is a column in request that will be formed for db all works.

    You can .label() any column of table, for example table.id.label("first_table_id")

    Login or Signup to reply.
  2. 0

    If you alias the model in your query using orm.aliased (guide), .mappings() will use the name of the alias as the key, for example this code aliases a User class to MyUser:

    with orm.Session(engine) as s:
    MyUser = orm.aliased(User, name='MyUser')
    q = sa.select(MyUser, sa.func.count(Address.id)).join(MyUser, MyUser.id == Address.user_id).group_by(MyUser.id)
    res = s.execute(q).mappings().all()
    for row in res:
        print(row)

    and generates this output

    {'MyUser': <__main__.User object at 0x7f4ad64ec680>, 'count': 2}
{'MyUser': <__main__.User object at 0x7f4ad64ec6b0>, 'count': 2}
{'MyUser': <__main__.User object at 0x7f4ad64ec6e0>, 'count': 2}
{'MyUser': <__main__.User object at 0x7f4ad64ec710>, 'count': 2}
{'MyUser': <__main__.User object at 0x7f4ad64ec740>, 'count': 2}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search