Incorrect SQL generated by SQLAlchemy

Question

I have a function that returns a query that would fetch «New priorities for emails» by given account id. First it selects a domain name for that account, and then selects a data structure for it. And everything should be OK IMO, but not at this time: SQLAlchemy is generating SQL that is syntactically wrong, and I can’t understand how to fix it. Here are the samples:

def unprocessed_by_account_id(account_id: str):
    account_domain = select(
        [tables.organizations.c.organization_id]).select_from(
        tables.accounts.join(
            tables.email_addresses,
            tables.accounts.c.account_id == tables.email_addresses.c.email_address,
        ).join(tables.organizations)
    ).where(
        tables.accounts.c.account_id == account_id,
    )
    domain_with_subdomains = concat('%', account_domain)
    fields = [
        tables.users.c.first_name,

        …
        tables.priorities.c.name,
    ]
    fromclause = tables.users.join(
        …
    ).join(tables.organizations)
    whereclause = and_(
        …
        tables.organizations.c.organization_id.notlike(
            domain_with_subdomains),
    )
    stmt = select(fields).select_from(fromclause).where(whereclause)

    return stmt

print(unprocessed_by_account_id(‘foo’))

So it generates:

SELECT
  users.first_name,
  …
  priorities.name
FROM (SELECT organizations.organization_id AS organization_id
      FROM accounts
        JOIN email_addresses
          ON accounts.account_id = email_addresses.email_address
        JOIN organizations
          ON organizations.organization_id = email_addresses.organization_id
      WHERE accounts.account_id = :account_id_1), users
  JOIN
    …
  JOIN organizations
    ON organizations.organization_id = email_addresses.organization_id
WHERE emails.account_id = :account_id_2 AND
      priorities_new_emails.status = :status_1 AND
      organizations.organization_id NOT LIKE
      concat(:concat_1, (SELECT organizations.organization_id
                         FROM accounts
                           JOIN email_addresses ON accounts.account_id =
                                                   email_addresses.email_address
                           JOIN organizations
                             ON organizations.organization_id =
                                email_addresses.organization_id
                         WHERE accounts.account_id = :account_id_1))

But the first

(SELECT organizations.organization_id AS organization_id
      FROM accounts
        JOIN email_addresses
          ON accounts.account_id = email_addresses.email_address
        JOIN organizations
          ON organizations.organization_id = email_addresses.organization_id
      WHERE accounts.account_id = :account_id_1)

Is redundant here and produces

[2017-05-29 23:49:51] [42601] ERROR: subquery in FROM must have an alias
[2017-05-29 23:49:51] Hint: For example, FROM (SELECT ...) [AS] foo.
[2017-05-29 23:49:51] Position: 245

I tried to use account_domain = account_domain.cte(), but no luck, except that the subquery went to WITH clause as expected. Also I tried with_only_columns with no effect at all. I think that Alchemy is adding this statement, because it sees it inside WHERE clause and thinks that without it the filtering will result in an error, but I’m not sure. Also I must mention than in previous version of code the statement was almost the same except there were no concat(‘%’, account_domain) and notlike was !=. Also I tried inserting alias here and there, but had no success with that either. And if I manually delete that first statement from the select is plain SQL, then I’d receive expectable results.

Any help is appreciated, thank you.

Answer 1

If you're using a subquery as a value, you need to declare it as_scalar():

domain_with_subdomains = concat('%', account_domain.as_scalar())