Prefer SD or HD channel when advanced recording on keyword

[janoonk]

In the past when the HD channels were still named ".... HD" I could easily make a Advanced recording like this:
`title like '%Christmas%' and genres like '%Movie / Drama%' and channel_oid in (select oid from channel where name not like '% HD')`


But the channels have been renamed by the provider and all are now HD and the ones that are still SD has SD in their channel name for example "RTL" (=HD) and "RTL SD" (=SD).
I want to record all EPG events that are a movie and contain a keyword for example `Christmas`. But most of the time the event is broadcasted on both SD and HD channels. To save some diskspace and prevent double recordings I want to prefer HD over SD (or vice versa).
To complicate it further sometimes the movie is broadcasted on even more than 2 channels for example: RTL, RTL SD and Film1 Family. How to prevent double recordings of the same movie or is this automatically done by NextPVR?

Am I correct that the advanced textbox can only contain a filter and subquery or can I also do a complete custom SQL query?

[mvallevand]

Can't you simply reverse the logic and for HD they are "not like %SD" and "like %SD" for SD

If you have a good EPG source like Schedules that provides a unique ID than duplicates will not be recorded. If it doesn't have a unique id the if the source has the movie year and identifies it as a movie it is possible that NextPVR generates an internal unique id.

If you turn on ExtendedLogging in config.xml, not to be confused with Verbose logging, you will see how it is constructed in the logs

Code:

SQL: select * from EPG_EVENT where ... order by start_time

your query would replace the ellipsis

Martin

[janoonk]

I managed to get it working with some SQL magic. It prefers SD and it will prevent double recordings even when on multiple channels!

Code:

select  *
from EPG_EVENT
where title like '%Christmas%'
and genres like '%Movie / Drama%'
and oid in (
select oid
from (
select ee2.oid,1 as nr
from EPG_EVENT ee2, CHANNEL c
where ee2.channel_oid=c.oid
and ee2.title=EPG_EVENT.title
and c.name like '% SD'
UNION
select ee2.oid,2 as nr
from EPG_EVENT ee2, CHANNEL c
where ee2.channel_oid=c.oid
and ee2.title=EPG_EVENT.title
and c.name not like '% SD'
)
order by nr asc
limit 1
)
order by title

and by creating an index on title column. Query time went from 40sec to 0,2s !

Code:

CREATE INDEX EPG_EVENT_title
ON EPG_EVENT(title);

[janoonk]

I managed to get it working with some SQL magic. It prefers SD and it will prevent double recordings even when on multiple channels!
Made a small fix/update for some edge case where a epg_event with a certain name existed multiple times and with two different genres (it was both a movie / drama and a regular broadcast) and were two different things.
If you prefer HD over SD: change `ORDER BY nr ASC` into `ORDER BY nr DESC`
Just copy/paste below in Advanced recording textbox:

Code:

genres LIKE '%Movie / Drama%'
AND oid IN (
    SELECT oid
    FROM (
        SELECT ee2.oid, 1 AS nr
        FROM EPG_EVENT ee2, CHANNEL c
        WHERE ee2.channel_oid = c.oid
        AND ee2.title = EPG_EVENT.title
        AND ee2.genres LIKE '%Movie / Drama%'
        AND c.name LIKE '% SD'
        
        UNION
        
        SELECT ee2.oid, 2 AS nr
        FROM EPG_EVENT ee2, CHANNEL c
        WHERE ee2.channel_oid = c.oid
        AND ee2.title = EPG_EVENT.title
        AND ee2.genres LIKE '%Movie / Drama%'
        AND c.name NOT LIKE '% SD'
    )
    ORDER BY nr ASC
        limit 1
)
AND imdb_rating >= 8
AND imdb_votes >= 10000

and by creating multiple indexes: title, genres, name columns. Query time went from 40sec to 0,2s !

Code:

CREATE INDEX EPG_EVENT_title
ON EPG_EVENT(title);

[janoonk]

I updated above query to prevent double recordings.
Use this query in NextPVR as Advanced Recording type (be sure to name it "Top movies" as the query uses this name):

Code:

genres LIKE '%Movie / Drama%'
AND oid IN (
    SELECT oid     
    FROM (
        SELECT ee2.oid,1 AS nr         
        FROM EPG_EVENT ee2,CHANNEL c
        WHERE ee2.channel_oid = c.oid
        AND ee2.title = EPG_EVENT.title         
        AND ee2.genres LIKE '%Movie / Drama%'         
        AND c.name LIKE '% SD'
        UNION                  
        SELECT ee2.oid,2 AS nr         
        FROM EPG_EVENT ee2,CHANNEL c         
        WHERE ee2.channel_oid = c.oid         
        AND ee2.title = EPG_EVENT.title         
        AND ee2.genres LIKE '%Movie / Drama%'         
        AND c.name NOT LIKE '% SD'     
    )     
    ORDER BY nr ASC
    limit 1
)
AND imdb_rating >= 8
AND imdb_votes >= 10000
and title not in (
    select DISTINCT(name)
    from SCHEDULED_RECORDING sr
    where sr.filename like '%/Top movies/%'
    and sr.status=2
)

If you want to test the query in like DB Browser for SQLite use this query (adjust columns in first select if you want):

Code:

select e.title,c.name
from EPG_EVENT e, CHANNEL c
where e.channel_oid=c.oid
and genres LIKE '%Movie / Drama%'
AND e.oid IN (
    SELECT oid     
    FROM (
        SELECT ee2.oid,1 AS nr         
        FROM EPG_EVENT ee2,CHANNEL c
        WHERE ee2.channel_oid = c.oid
        AND ee2.title = e.title         
        AND ee2.genres LIKE '%Movie / Drama%'         
        AND c.name LIKE '% SD'
        UNION                  
        SELECT ee2.oid,2 AS nr         
        FROM EPG_EVENT ee2,CHANNEL c         
        WHERE ee2.channel_oid = c.oid         
        AND ee2.title = e.title         
        AND ee2.genres LIKE '%Movie / Drama%'         
        AND c.name NOT LIKE '% SD'     
    )     
    ORDER BY nr ASC
    limit 1
)
AND imdb_rating >= 8
AND imdb_votes >= 10000
and e.title not in (
    select DISTINCT(name)
    from SCHEDULED_RECORDING sr
    where sr.filename like '%/Top movies/%'
    and sr.status=2
)