CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.1
Panama Jack
Panama Jack

Reputation: 24448

SELECT data from 1st table whether exists or not in 2nd table but with conditions

I need some help from some sql/postgres guru's. What I'm trying to accomplish is I have a table for tracks, genres and playlists. I need to select from table tracks if it already exists in track_playlists or not exists. Which I have kind of done already. Here is current code.

SELECT tr.*, MAX(tp.played) as last FROM tracks as tr 
LEFT JOIN track_genres as tg ON tr.trk_id = tg.trk_id 
LEFT JOIN track_playlists as tp ON tr.trk_id = tp.trk_id 
AND tp.mem_id=12345 AND tp.chn_id=9 
AND tp.played > EXTRACT(epoch from NOW() - INTERVAL '4 hours') 
AND (tr.artist != tp.artist AND tp.played > EXTRACT(epoch from NOW() - INTERVAL '1 hours'))
WHERE tg.rotation=2 GROUP BY tr.trk_id ORDER BY last ASC LIMIT 6

Example scenario:

Table tracks 

trk_id | artist  | title
-------------------------
1      | artist1 | title1
2      | artist1 | title2
3      | artist2 | title1
4      | artist3 | title1
5      | artist4 | title1
6      | artist5 | title1
7      | artist1 | title3
8      | artist6 | title1

Table track_genres

gen_id | trk_id | rotation
--------------------------
1      | 1       | 9 
2      | 2       | 9
3      | 3       | 2
4      | 4       | 3
5      | 5       | 9
6      | 6       | 2
7      | 7       | 10
8      | 8       | 9

Table track_playlists

pla_id | mem_id  | trk_id | chn_id | artist  | title | played
-------------------------------------------------------------

Here are the tables that I am dealing with. Currently when a user starts out the track_playlist for them is empty. When they select a genre it should populate this table but not all at once hence the Limit clause, and it needs to select songs based on whether there are songs already or not in this table for the user. If there aren't any songs then it selects the songs via select query I'm trying. As the songs play, when it goes to pull new songs it needs to see what songs are already there in track_playlist. If the song is already in the playlist table then it should skip it and only pull songs that are not in the playlist table yet for this user. Once all songs are finally in the playlist for the user, it just needs to check the played timestamp to see if the last time played for that song is greater than 4 hours and that it's the oldest track by timestamp (least recently played) and not an artist that has played in last 1 hour.

I hope this is a bit more clear.

I used this answer to help me with the query of empty or not.

sql update data in 2nd db from 1st db only where table exists

Upvotes: 0

Views: 125

Answers (1)

cha
cha

Reputation: 10411

I think you will need two sub-queries: Last played time per artist:

select artist, max(played) 
from track_playlists 
WHERE mem_id=12345 AND chn_id=9 
GROUP BY artist

Last Played time per track:

select trk_id, max(played) 
from track_playlists 
WHERE mem_id=12345 AND chn_id=9 
GROUP BY trk_id

Then join your track and genre tables to them applying the conditions (you will need to use LEFT JOIN to ensure that new track and artists are always included). You will also need an extra check for the last played artist using the EXISTS, like this:

SELECT * FROM(
SELECT tr.*, tl.track_last_played, al.artist_last_played,
ROW_NUMBER() OVER(PARTITION BY tr.artist) rn
FROM tracks as tr 
LEFT JOIN track_genres as tg ON tr.trk_id = tg.trk_id 
LEFT JOIN (select artist, max(played) as artist_last_played 
           from track_playlists 
           WHERE mem_id=12345 AND chn_id=9
           GROUP BY artist) as al 
   ON tr.artist = al.artist
LEFT JOIN (select trk_id, max(played) as track_last_played 
           from track_playlists 
           WHERE mem_id=12345 AND chn_id=9
           GROUP BY trk_id) as tl 
   ON tr.trk_id = tl.trk_id 
WHERE (tl.track_last_played IS NULL OR tl.track_last_played < EXTRACT(epoch from NOW() - INTERVAL '4 hours') )
AND NOT EXISTS (SELECT 1 FROM track_playlists WHERE mem_id=12345 AND chn_id=9 
                AND artist = tr.artist                
                AND played > EXTRACT(epoch from NOW() - INTERVAL '1 hours'))
) a
WHERE rn = 1
ORDER BY track_last_played, artist_last_played ASC

SQL Fiddle

Upvotes: 2

Related Questions