[mythtv] Power Recording Rule excluding genres

[John P Poet]

On Mon, Dec 10, 2018 at 7:51 PM David Engel <david at istwok.net> wrote:

> 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
>

Thank you, David.   Unfortunately, that JOIN clause does not fit in
mythconverg.record.subtitle.  It gets chopped off before the "on
program.chanid ...".

When I try it directly in mysql, it complains "Unknown column
'program.chanid' in 'on clause'".

It looks like if I want to do this using a "Power Recording Rule" some
special support may need to be added to mythbackend to deal with the
situation.


John
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.mythtv.org/pipermail/mythtv-dev/attachments/20181211/58c6164d/attachment.html>