Mysql – Complex user interests query

MySQL

I'm trying to create a query that will return a list of results based on a series of conditions. For example – a list of movies based on interests/preferences.

Example Source Data:

+--------+---------------+-------+-------------+--------+
|MovieID | ShortName     | Genre |  Time(Mins) | Action |
+--------+---------------+-------+-------------+--------+
|   1    | LOTR 1        |Fantasy|    208      |   TRUE |
+--------+---------------+-------+-------------+--------+
|   2    | LOTR 2        |Fantasy|    223      |   TRUE |
+--------+---------------+-------+-------------+--------+
|   18   | Harry Potter 1|Fantasy|    174      |   TRUE |
+--------+---------------+-------+-------------+--------+
|   45   | Love Actually |Romance|    145      |  FALSE |
+--------+---------------+-------+-------------+--------+

A separate table contains a limited series of filters directly related to columns of data. For example:

Filter 1: ShortName (Contains) "LOTR" && Time (IsGreaterThan) 220 Minutes
Filter 2: ShortName (Equals) "LOTR 2"
Filter 3: Action (Is) "FALSE" && Time (IsGreaterThan) 60 Minutes
Filter 4: Genre (IsNot) "Fantasy" && Time (IsLessThan) 180 Minutes

Filter 1 returns: MovieID (1,2)
Filter 2 returns: MovieID (2)
Filter 3 returns: MovieID (45)
Filter 4 returns: MovieID (45)

Ignore the way results are shown above, it's just to exemplify the returned values.

I need the table of results to be returned in 1 execution of the query.
I'm skilled enough in SQL, however due to legacy platform constraints, I have to achieve this without using looping (While/For) functions.
Is there a name for this kind of query (to return a list of values from a table based on a complex filter statement), and can someone provide an example query to help me to understand the concept.

Please let me know in the comments if you require more information to help.

Best Answer

WITH cte AS 
(
SELECT *, 
       CASE field WHEN 'ShortName'
                  THEN CASE operator WHEN 'Contains'
                                     THEN ShortName LIKE CONCAT('%', criteria, '%')
                                     WHEN 'Equal'
                                     THEN ShortName = criteria
                                     ELSE 0
                                     END
                  WHEN 'Genre'
                  THEN CASE operator WHEN 'Is'
                                     THEN Genre = criteria
                                     WHEN 'IsNot'
                                     THEN Genre != criteria
                                     ELSE 0
                                     END
                  WHEN 'Time_Mins'
                  THEN CASE operator WHEN 'IsGreaterThan'
                                     THEN Time_Mins > criteria
                                     WHEN 'IsLessThan'
                                     THEN Time_Mins < criteria
                                     ELSE 0
                                     END
                  WHEN 'Action'
                  THEN CASE operator WHEN 'Is'
                                     THEN Action = criteria
                                     WHEN 'IsNot'
                                     THEN Action != criteria
                                     ELSE 0
                                     END
                  ELSE 0
                  END test
FROM movies
CROSS JOIN filters
)
SELECT MovieID, ShortName, Genre, Time_Mins, Action, filterid
FROM cte
GROUP BY MovieID, ShortName, Genre, Time_Mins, Action, filterid
HAVING SUM(test=0)=0
ORDER BY MovieID, filterid;

fiddle

Pay attention to formalized filters storing format.

You may add possible operator/criteria values by adding according variants into CASEs.

For example, if you want to add a filtering variant for to select strict time you must add WHEN 'Equal' THEN Time_Mins = criteria to Time_Mins checking CASE.

But do not forget that criteria datatype is STRING! And Action datatype is string too. Use proper data type convertion when default convertion is not obvious.

Related Question