python - 如何使用 SQLAlchemy 在 SELECT X FROM ... 中的位置 X 中使用子查询？

Question

给定数据库中的下表

CREATE TABLE users (
  id SERIAL NOT NULL, 
  name VARCHAR, 
  PRIMARY KEY (id)
)

CREATE TABLE phones (
  id SERIAL NOT NULL, 
  user_id INTEGER, 
  phone VARCHAR, 
  PRIMARY KEY (id), 
  FOREIGN KEY(user_id) REFERENCES users (id)
)

CREATE TABLE emails (
  id SERIAL NOT NULL, 
  user_id INTEGER, 
  email VARCHAR, 
  PRIMARY KEY (id), 
  FOREIGN KEY(user_id) REFERENCES users (id)
)

我想知道每个用户有多少个电子邮件地址和电话号码。

下面的查询有效:

SELECT
  users.id AS id,
  users.name AS name,
  (
    SELECT COUNT(*)
    FROM emails WHERE emails.user_id = users.id
  ) AS email_count,
  (
    SELECT COUNT(*)
    FROM phones WHERE phones.user_id = users.id
  ) AS phone_count
FROM users;

但是当 SQLAlchemy 生成这样的查询时:

SELECT
  users.id,
  users.name,
  email_count.count_1,
  phone_count.count_2
FROM
  users,
  (
    SELECT count(*) AS count_1
    FROM emails, users
    WHERE emails.user_id = users.id
  ) AS email_count,
  (
    SELECT count(*) AS count_2 
    FROM phones, users
    WHERE phones.user_id = users.id
  ) AS phone_count

我尝试使用 GROUP BY 并加入他们，但由于某些用户没有电子邮件和电话，他们被排除在外，我不喜欢 OUTER JOIN 中的 NULL。

Answer 1

更新的 SQL:

select e.id, e.name, distinct_email, distinct_phone
from
(
   select u.id, u.name, count(distinct e.id) as distinct_email
   from users as u
   left join emails as e
   on u.id = e.user_id
   group by u.id, u.name
) AS e
full outer join
(
   select u.id, u.name, count(distinct p.id) as distinct_phone
   from users as u
   left join phones as p
   on u.id = p.user_id
   group by u.id,  u.name
) AS p
on e.id = p.id