Ignore WHERE clause when record isn't present

user_9397534 0
Viewed 60

I know there're plenties of topics with similar title but they seem to be related to other cases. I have 3 tables:

song:
  id
  title


playlist_song:
  id
  song_id
  playlist_id


stats_record:
  id
  type // enum SONG_LIKED/SONG_DISLIKED/...
  user_id
  entity_id // id of an entity stats record refers to

I use playlist_song for storing songs in playlists and stats_record for capturing things like plays/likes/dislikes.

I have a functionality where I want to show a user songs that they never liked or disliked. Meaning stats_record either doesn't exist or its type != SONG_LIKED/SONG_DISLIKED (for that specific user!)

However, when I try to construct this query it'd show songs that was never liked by other users as well (where I care only about current user). My query looks like this:

select s.title, sr.type 
from song s
left join playlist_song ps on ps.song_id = s.id
left join stats_record sr on sr.entity_id = ps.id
where ps.playlist_id = 'a686e0da-750f-11eb-9da1-bf79c63bacf7' 
  and ((sr.user_id = 'a26e23b4-7483-11eb-986b-5fc23c9ef85c' 
  and (not sr.type = 'SONG_LIKED' 
  and not sr.type = 'SONG_DISLIKED')) or sr.id is null);

what I want to achieve is something like "if stats_record exists, check whether it's not SONG_LIKED/DISLIKED, otherwise ignore"

postgresql sql
2 Answers

I haven't tested this, but I would think an anti-join would work:

select s.title, sr.type 
from song s
left join playlist_song ps on ps.song_id = s.id
where
  ps.playlist_id = 'a686e0da-750f-11eb-9da1-bf79c63bacf7' and
  not exists (
    select null
    from stats_record sr
    where
      sr.entity_id = ps.id and
      sr.user_id = 'a26e23b4-7483-11eb-986b-5fc23c9ef85c' and
      sr.type in ('SONG_LIKED', 'SONG_DISLIKED') -- this might be unnecessary
  )

This construct is also very efficient, and will be tolerant of duplicates, although I don't think you would have any in this example.

Assuming this works, try omitting that last where clause (sr.type in...). I don't think you'll need it.

user_10018840

Have you tried something like this?

select s.title, sr.type 
from song s
left join playlist_song ps on ps.song_id = s.id
left join stats_record sr on sr.entity_id = ps.id
where ps.playlist_id = 'a686e0da-750f-11eb-9da1-bf79c63bacf7' 
  and sr.user_id = 'a26e23b4-7483-11eb-986b-5fc23c9ef85c' 
  and (sr.type not in('SONG_LIKED','SONG_DISLIKED') or sr.id is null);
user_150371880
Related