Home / Questions / 118755
Accepted
Bernardo Harreguy
Asked: 2020-11-21 04:34:32 +0800 CST 2020-11-21 04:34:32 +0800 CST

Problem with SQLite query

  • 772

the query that I want to perform is about the selection of a musical theme with certain specifications, to make a playlist, it is generated as the themes are played, the query is something like selecting the next song. In the table I have a logical data like, "played" where it turns to true when it is played and "lastplayed" where the DATETIME is placed at the time of playback. Here is the database schema:

albums table

CREATE TABLE "albums" ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `nombre` TEXT );

music table

CREATE TABLE "musica" ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, `nombreyruta` TEXT NOT NULL, `anulado` NUMERIC NOT NULL DEFAULT 0, `album` INTEGER, `reproducido` INTEGER NOT NULL DEFAULT 0, `ultimareproduccion` TEXT );

Reproduction table

CREATE TABLE "reproduccion" ( `Id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, `idalbum` INTEGER NOT NULL DEFAULT 0, `habilitado` INTEGER NOT NULL DEFAULT 1 )

I would need that in the selection of the next theme, it is based on a SELECT query, that it chooses the next theme to play in the following way, that it has the data PLAYBACK = 0, and that it avoids selecting those that have been played recently, and also, that within this in a percentage you can perform a Ramdom, or random selection. An example, the playback theme is requested to be ordered from highest to lowest in "LAST PLAY" and that from the 40% of these oldest, it can be selected randomly. The main idea is that the playlist is not repetitive, that is, that the list is not always the same, and that the songs are not heard one after the other.

Here I leave the select that I have to preview, what it does is select all the songs that can be chosen for playback:

SELECT musica.id from musica inner join reproduccion on musica.album = reproduccion.idalbum  WHERE musica.reproducido = 0;

As you can see, the playback is not carried out in the database, but is done when you ask with a SELECT which is the next song to play. which is what it would take to solve this question!

I hope you can help me thank you very much

sql

1 Answers

  1. Best Answer

    To calculate 40% of the songs stored in the table musicawe can use COUNT(*)and multiply it by the desired value (0.4 for 40%):

    SELECT CAST(COUNT(*) * 0.4 AS int) FROM musica

    Since the result is a floating point number, it must be converted to an integer with a CAST.

    That result can be used in the SQL clause LIMIT:

    SELECT *
FROM musica m
ORDER BY ultimareproduccion DESC
LIMIT (
  SELECT CAST(COUNT(*) * 0.4 AS int) + 1 FROM musica
)

    I have added 1 to the 40% calculation to round up and avoid not getting any record when it is 0.

    Now we can use ORDER RANDOM()to unscramble that 40% of records obtained.

    The function random()returns random values ​​for each previously selected record, so the records will be reordered according to that random value.

    Finally, it only remains to stay with one of them with LIMIT 1:

    SELECT c.*
FROM (
  SELECT *
  FROM musica m
  ORDER BY ultimareproduccion DESC
  LIMIT (
    SELECT CAST(COUNT(*) * 0.4 AS int) + 1 FROM musica
  )
) c
ORDER BY RANDOM()
LIMIT 1

    Online example of the proposed SQL: https://www.db-fiddle.com/f/3quzKL3kjJ7MGjY2FKCksJ/0

    • 2