Postgres:在列上选择Distinct不会通过Joins返回不同的结果 [英] Postgres: Selecting Distinct on a column is not returning distinct results with Joins
问题描述
我正在建立一个包含以下表格的系统:
I'm building a system in which there are the following tables:
- 歌曲
- 广播
- 站
- 关注
- 用户
用户关注电台,电台通过广播播放歌曲.
A user follows stations, which have songs on them through broadcasts.
我正在根据用户关注的电台为他们构建歌曲的供稿".
I'm building a "feed" of songs for a user based on the stations they follow.
以下是查询:
SELECT DISTINCT ON ("broadcasts"."created_at", "songs"."id") songs.*
FROM "songs"
INNER JOIN "broadcasts" ON "songs"."shared_id" = "broadcasts"."song_id"
INNER JOIN "stations" ON "broadcasts"."station_id" = "stations"."id"
INNER JOIN "follows" ON "stations"."id" = "follows"."station_id"
WHERE "follows"."user_id" = 2
ORDER BY broadcasts.created_at desc
LIMIT 18
正如您在此处看到的那样,我收到的重复歌曲正在发生,因为我正在跟踪两个都广播了同一首歌曲的电台:
As you can see here, I'm getting duplicate songs which is happening because I'm following two stations that BOTH have broadcasted that same song:
注意:shared_id与id相同.
Note: shared_id is the same as id.
我的问题是:如何修改此查询,使其仅根据其ID(或shared_id)返回唯一的歌曲?
推荐答案
从DISTINCT
谓词中删除"broadcasts"."created_at"
,因为它返回非唯一值,因此重复了songs
.如果您需要使用此信息进行排序,请创建一个新问题.
Remove "broadcasts"."created_at"
from the DISTINCT
predicate because it is returning non-unique values, hence duplicate songs
. If you need to use this information to sort please create a new question.
这篇关于Postgres:在列上选择Distinct不会通过Joins返回不同的结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!