Author Topic: Query sql  (Read 3411 times)

neverever

  • Jr. Member

  • Offline
  • **
  • 85
Re: Query sql
« Reply #15 on: May 26, 2017, 02:12:58 PM »
Almost... It picks 1 Rock song not been played for the longest time (considering your repetition rules). If you want it randomly you need to apdapt it slightly this way:

Code: [Select]

select s.ID
from songs s
join genre g
 on g.id = s.id_genre
where g.name = 'Rock'
 and TIMESTAMPDIFF(MINUTE, s.artist_played, NOW()) > $ArtistRepeatInterval$
 and TIMESTAMPDIFF(MINUTE, s.date_played, NOW()) > $TrackRepeatInterval$
order by RAND()
limit 1



You should also make sure that it doesn't pick a song already queued and it's not disabled...

Code: [Select]

select s.ID
from songs s
join genre g
 on g.id = s.id_genre
where g.name = 'Rock'
 and s.enabled = 1
 and LOWER(TRIM(s.artist)) not in (
SELECT LOWER(TRIM(q.artist))
FROM queuelist q
)
 and LOWER(TRIM(s.title)) not in (
SELECT LOWER(TRIM(s1.title))
FROM songs s1
JOIN queuelist q
ON q.songID = s1.ID
)
 and TIMESTAMPDIFF(MINUTE, s.artist_played, NOW()) > $ArtistRepeatInterval$
 and TIMESTAMPDIFF(MINUTE, s.date_played, NOW()) > $TrackRepeatInterval$
order by RAND()
limit 1



Radiobellissima

  • Full Member

  • Offline
  • ***
  • 188
Re: Query sql
« Reply #16 on: May 26, 2017, 07:30:23 PM »
Test it.

Radiobellissima

  • Full Member

  • Offline
  • ***
  • 188
Re: Query sql
« Reply #17 on: May 27, 2017, 08:21:22 PM »
It works fine ... thank you. :D :D :D

You are a great one. :ok:

Radiobellissima

  • Full Member

  • Offline
  • ***
  • 188
Re: Query sql
« Reply #18 on: June 13, 2017, 12:15:27 PM »

Another small request, given the great potential of un-exploited software. I would need another script to play a subcategory out of a certain genre. Example:
Category / music
subcategory / Top40
genre (not to play) Summer.

neverever

  • Jr. Member

  • Offline
  • **
  • 85
Re: Query sql
« Reply #19 on: June 13, 2017, 04:27:44 PM »
Probably you'll need to harmonize the way of working with Genre tag. On the one hand you are using music genres like Rock, on the other hand something like "Summer". But anyway... this should do the trick (untested):

Code: [Select]

select s.ID
from songs s
join genre g
 on g.id = s.id_genre
join subcategory sc
 on sc.id = s.id_subcat
join category c
 on c.id = sc.parentid
where c.name = 'Music'
 and sc.name = 'Top40'
 and g.name != 'Summer'
 and s.enabled = 1
 and LOWER(TRIM(s.artist)) not in (
SELECT LOWER(TRIM(q.artist))
FROM queuelist q
)
 and LOWER(TRIM(s.title)) not in (
SELECT LOWER(TRIM(s1.title))
FROM songs s1
JOIN queuelist q
ON q.songID = s1.ID
)
 and TIMESTAMPDIFF(MINUTE, s.artist_played, NOW()) > $ArtistRepeatInterval$
 and TIMESTAMPDIFF(MINUTE, s.date_played, NOW()) > $TrackRepeatInterval$
order by RAND()
limit 1


Radiobellissima

  • Full Member

  • Offline
  • ***
  • 188
Re: Query sql
« Reply #20 on: June 13, 2017, 08:46:26 PM »
Test it.


Radiobellissima

  • Full Member

  • Offline
  • ***
  • 188
Re: Query sql
« Reply #21 on: June 16, 2017, 10:05:04 PM »
No work play only music/top40
Makes no difference whether summer or not :-[