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

Postgresql – SQLAlchemy: I can't execute query with two tables

My problem next: I can’t execute query, where need to get list of Accounts with their Records by account_id as sum of Records.

My query next:

subq = (
    select(
        func.sum(Record.amount).label('amount')
    )
    .filter(Record.account_id == Account.uuid)
    .subquery()
)
        
accounts = (
    await self.session.execute(
        select(Account)
        .join(subq, Account.uuid == Record.account_id)
        .filter(Account.creator_id == uuid)  # uuid = it's User ID (Creator)
    )
).scalars().all()

Also, Record table looks like this:

[
    {
      "type": "income",
      "amount": 2000,
      "uuid": "cf876a3d-3395-4f5f-82b5-b496a66e107c"
    },
    {
      "type": "income",
      "amount": 5000,
      "uuid": "fe25274d-111f-410c-a18c-04c73cbcc9db"
    },
    {
      "type": "expense",
      "amount": 3000,
      "uuid": "7a151849-fb8e-47dc-96a0-50fef12233e8"
    },
    {
      "type": "expense",
      "amount": 750,
      "uuid": "dd90988a-cd53-4125-8017-2e2ed05ec48c"
    }
]

Desired result is:

[
  {
    "uuid": "38528eff-61d8-4210-8f9b-7739414dff4a",
    "name": "newAccount",
    "creator_id": "15fvh85j-012f-41bh-9km5-e22c8t78e321",
    "is_private": false,
    "amount": 25000
  },
  {
    "uuid": "86256mdjv-40m7-96v3-4de1-1e946b356gs8",
    "name": "personalAccount",
    "creator_id": "867becf0-c86a-4551-af81-
    "is_private": false,
    "amount": 53500
  }
]

But I got only the same without amount.
What I do wrong?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I found the answer that solved my problem, this using join and mappings().all():

    
accounts = (
    await self.session.execute(
        select(
            Account.uuid,
            Account.name,
            Account.creator_id,
            Account.is_private,
            func.coalesce(func.sum(Record.amount), 0).label('amount')
        )
        .filter(Account.creator_id == uuid)
        .join(Record, Record.account_id == Account.uuid, isouter=True)
        .group_by(Account.uuid)
    )
).mappings().all()

  2. 0

    I can see several issues with your queries:

    1- A groupby clause is missing from your sub-query. You need to be selecting the account_id and the sum(amount) grouped by account_id. This will give us the sum of amount for each account.

    2- In your main query, you are only selecting fields from Account

    3- The join condition in your main query is wrong and you are not specifying any keys from subq

    Here’s a possible fix:

    from sqlalchemy import func, select

subq = (
    select(
        Record.account_id,
        func.sum(Record.amount).label('amount')
    )
    .group_by(Record.account_id)
    .subquery()
)

accounts = (
    await self.session.execute(
        select(Account, subq.c.amount)
        .join(subq, Account.uuid == subq.c.account_id, isouter=True)
        .filter(Account.creator_id == uuid)
    )
).all()
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search