[mythtv] Power Recording Rule excluding genres

[David Engel]

On Mon, Dec 10, 2018 at 05:59:56PM -0700, John P Poet wrote:
> I would like to set up a "Power Recording Rule" which records all PBS
> movies as long as the genre is NOT Comedy, Historical, Romance or History.
> 
> I am able to achieve rejecting one genre with a syntax like this:
> 
> channel.callsign LIKE 'PBS%HD' AND program.category_type = 'movie' AND
> 'Comedy' <> ALL (select genre from programgenres where
> program.chanid=programgenres.chanid AND
> program.starttime=programgenres.starttime)
> 
> However, that gets ugly real quick, if I want to negate more than one genre:
> 
> channel.callsign LIKE 'PBS%HD' AND program.category_type = 'movie' AND
> 'Comedy' <> ALL (select genre from programgenres where
> program.chanid=programgenres.chanid AND
> program.starttime=programgenres.starttime) AND 'Historical' <> ALL (select
> genre from programgenres where program.chanid=programgenres.chanid AND
> program.starttime=programgenres.starttime) AND 'Romance' <> ALL (select
> genre from programgenres where program.chanid=programgenres.chanid AND
> program.starttime=programgenres.starttime) AND 'History' <> ALL (select
> genre from programgenres where program.chanid=programgenres.chanid AND
> program.starttime=programgenres.starttime)
> 
> I figured I could put the sub query into a derived table and then just use
> that when doing the "genre <> ALL (results)" but either that does not or I
> have not figured out the syntax.
> 
> Anyone have any suggestion?

I don't have time to work out and test a complete solution but I think
something like this will work.

For joinging, use

    join ( select chanid, starttime,
                  group_concat(genre order by genre) allgenres
           from programgenres group by chanid, starttime ) g
         on program.chanid = g.chanid
            and program.starttime = g.starttime

The subquery will give you results similar to

+--------+---------------------+-------------------------------------------------------------------------------------------+
| chanid | starttime           | allgenres                                                                                 |
+--------+---------------------+-------------------------------------------------------------------------------------------+
|   6913 | 2018-11-26 00:03:00 | Documentary,Entertainment,Show,Special,tvshow                                             |
|   6260 | 2018-11-26 00:05:00 | Adventure,Children,Episode,series,Series,Show                                             |
|   6760 | 2018-11-26 00:05:00 | Adventure,Children,Episode,series,Series,Show                                             |
|   6901 | 2018-11-26 00:10:00 | Documentary,Episode,Newsmagazine,series,Series,Show                                       |

For your where clause, use something like

    NOT g.allgenres REGEXP '(^|,)(Comedy|Historical|Romance|History)(,|$)'

You'll probably have to fiddle with the regex.  Basically you want the
regex to match on any of the undesired genres preceeded by the
beginning of the string or a comma and succeeded by a comma or the end
of the string.

David
-- 
David Engel
david at istwok.net