How to write correct sql query for filtering and displaying movies?

Question

SQL

I have a table like this called joined_df:

+-------+--------------------+---------+----+-------+-----------------+
|movieId|               title|   genres|year|movieId|      avg(rating)|
+-------+--------------------+---------+----+-------+-----------------+
|   1580|Men in Black (a.k...|   Sci-Fi|1997|   1580|3.487878787878788|
|   1580|Men in Black (a.k...|   Comedy|1997|   1580|3.487878787878788|
|   1580|Men in Black (a.k...|   Action|1997|   1580|3.487878787878788|
|   2366|          King Kong |   Horror|1933|   2366|             3.64|
|   2366|          King Kong |  Fantasy|1933|   2366|             3.64|
|   2366|          King Kong |Adventure|1933|   2366|             3.64|
|   2366|          King Kong |   Action|1933|   2366|             3.64|
|   3175|       Galaxy Quest |   Sci-Fi|1999|   3175|             3.58|
|   3175|       Galaxy Quest |   Comedy|1999|   3175|             3.58|
|   3175|       Galaxy Quest |Adventure|1999|   3175|             3.58|
|   1088|      Dirty Dancing |  Romance|1987|   1088|3.369047619047619|
|   1088|      Dirty Dancing |  Musical|1987|   1088|3.369047619047619|
|   1088|      Dirty Dancing |    Drama|1987|   1088|3.369047619047619|
|  32460|Knockin' on Heave...|    Drama|1997|  32460|             4.25|
|  32460|Knockin' on Heave...|    Crime|1997|  32460|             4.25|
|  32460|Knockin' on Heave...|   Comedy|1997|  32460|             4.25|
|  32460|Knockin' on Heave...|   Action|1997|  32460|             4.25|
|  44022|Ice Age 2: The Me...|   Comedy|2006|  44022|3.217391304347826|
|  44022|Ice Age 2: The Me...| Children|2006|  44022|3.217391304347826|
|  44022|Ice Age 2: The Me...|Animation|2006|  44022|3.217391304347826|
+-------+--------------------+---------+----+-------+-----------------+
only showing top 20 rows

and i have variables to filter

ARG_GENRES = 'Sci-Fi|Action'       #by default = ''   фильтр по жанру 
YEAR_TO = 2007                     #by default = 2030. фильтр по году 
YEAR_FROM = 1900                   #by default = 1800  фильтр по году 
REGEXP = 'Terminator'              #by default = ''   фильтр по названию фильма
N = 5                              #by default = 10000  фильтр по количеству строк в выводимом результате

I planned to pass these arguments through the string format in the sql query

. The main problem with the query is related to the genre, that is, the fact that there can be several genres and genres can be listed through |

how can i write a sql query that will filter movies based on all filtering arguments?

For example:
if the filtering arguments are -N 4 -ARG_GENRES 'Action'

then the output should be:

genre;title;year;rating
Action;Tombstone ;1993;5.00
Action;Star Wars: Episode IV - A New Hope ;1977; 5.00
Action;Rob Roy ;1995;5.00
Action;Desperado ;1995;5.00

and if the arguments to filter are: -N 4 -ARG_GENRES 'Drama|Adventure' -YEAR_FROM 2015 -YEAR_TO 2020

then the result should be:
genre;title;year;rating
Drama;Wonder ;2017;5.00
Drama;Inside Out ;2015;5.00
Drama;Three Billboards Outside Ebbing, Missouri ;2017;5.00
Drama;The Martian ;2015;5.00
Adventure; Mad Max: Fury Road ;2015;5.00
Adventure;Rogue One: A Star Wars Story ;2016;5.00
Adventure;Spectre ;2015;5.00
Adventure;Star Wars: Episode VII - The Force Awakens ;2015;5.00

That is, if we have more than one genre is specified, then N (an argument for filtering by the number of output results) must be applied to each genre separately for

correct sorting:
genre (alphabetical), rating (desc), year of release (desc), and movie title (alphabetical)

Answer 1

Have you tried OR, AND operators?