postgresql - 将 COUNT 集成到 postgres 查询中

Question

我有一个名为users 的表，它下面是tracks 表，下面是likes 表。 users 表有一个用于用户 ID 的 id 列，tracks 表有一个用于轨道的 id 列ID 和拥有该轨道的用户 ID 的 user_ID 列。 likes 表有一个 user_id 列代表喜欢轨道的用户，还有一个 track_id 列代表轨道 ID。现在我的查询运行良好，但我想计算特定轨道 ID 的点赞次数。

我当前的获取请求:

cur.execute('SELECT t.title, t.share, t.id, u.id, u.username, l.date_liked, '
                'FROM (tracks t INNER JOIN users u ON t.user_id = u.id)'
                'LEFT JOIN (SELECT * FROM likes WHERE user_id = %s)'
                'l ON l.track_id = t.id ORDER BY t.id DESC', [current_user.id])   

说到 Postgres，我不是很精通。我知道我需要执行 SELECT COUNT(track_id) FROM likes，但我不确定如何将它集成到我当前的查询中。

根据 Alan Samets 的回答，我得出了这个结论:

cur.execute('WITH t_count AS'
                '(SELECT tracks.id,'
                        'COUNT(*) like_count '
                            'FROM tracks '
                                 'INNER JOIN likes '
                                     'ON tracks.id = likes.track_id '
                            'GROUP BY tracks.id)'
                ', t_mylike AS'
                '(SELECT likes.track_id, likes.date_liked FROM likes WHERE likes.user_id = %s )'
                'SELECT t.title, t.share, t.id, t_count.like_count, u.id, u.username, ml.date_liked '
                     'FROM tracks t '
                          'LEFT JOIN t_count '
                              'ON t.id = t_count.id '
                          'INNER JOIN users u '
                              'ON u.id = t.user_id '
                          'LEFT JOIN t_mylike ml ON t.id = ml.track_id '
                     'ORDER BY t.id DESC', [current_user.id])

这几乎可以完美运行，但是当用户喜欢他们自己的轨道时它不会正确返回。现在，当用户喜欢另一个用户的轨道时，它会返回被喜欢的日期，但是当用户喜欢他们的所有者轨道时，它会返回 None - 这是不正确的。

Answer 1

我相信 PostgreSQL 支持公用表表达式。我假设您正在编写的查询是为了驱动一个 UI 组件，并且您想要显示轨道信息、当前用户喜欢该轨道的日期以及喜欢的总数。

WITH t_count AS 
(
     SELECT tracks.id
     ,      COUNT(*) like_count
            FROM tracks
                 INNER JOIN likes
                     ON tracks.id = likes.track_id
            GROUP BY tracks.id
)
SELECT t.title, t.share, t.id, t_count.like_count, u.id, u.username, l.date_liked
     FROM tracks t
          INNER JOIN t_count
              ON t.id = t_count.id
          INNER JOIN users u
              ON u.id = t.user_id
          LEFT JOIN likes l
              ON u.id = likes.user_id
     WHERE user_id = %s
     ORDER BY ...