[mythtv-users] SQL Query Help

[Michael T. Dean]

On 01/18/2018 08:53 AM, Craig Huff wrote:
> Ok.  I see that my inquiry won't make sense without inflicting the 
> list with the long(er) story --
>
> I use the custom schedule for premieres with recordings disabled to 
> watch for new shows that I might be interested in.  When one comes up, 
> I set an override to record the first episode and place it into a 
> recording group labeled "Premieres".  Of late, I have been too busy 
> with other things to keep on top of them and when I _do_ get around to 
> watching one I like, I have missed several episodes that have aired in 
> the meantime.  I was trying to find a way to construct an SQL query 
> for recording rules that put recordings in the Premieres group (that 
> part was easy, just use 'where record.recgroup="Premieres"'), then 
> combine it with data from other tables (primarily the program and 
> recorded tables), filter down to only the ones that have a single 
> episode recorded or to be recorded, and then to discern when other 
> episodes become available for those programs so I could tell when to 
> create new recording rules to capture the rest of the series until I 
> get around to deciding if I'm interested in them.
>
> Yes, I could get there with more effort and attention to the various 
> menu choices in the frontend "Manage Recordings" menu sub-tree, but I 
> thought I could cover each and every such series premiere (sometimes 
> there are several at the same time) in one operation if I could come 
> up with the "right" SQL query.
>
> At the moment, the first episode of "The Alienist" will be airing on 
> the 29th and there are not yet any listings for subsequent episodes in 
> the program guide from Schedules Direct (too soon), so I thought this 
> would be a good trial candidate for my efforts.  I used the Gunsmoke 
> series for my test queries since I knew there were results to be found 
> if I could get my query syntax worked out.
>
> I know this would be imperfect because I wouldn't be taking into 
> account the various recording types (e.g. record one showing daily, 
> record only on this day, etc.), but that would be okay because my 
> intended use case would normally be based on rules for the "record all 
> showings" recording type.
>
> Given the feedback thus far, I will be looking into what I can do with 
> the oldrecorded table, which I hadn't considered before.
>
> I'd rather stick with an SQL query, if it's doable, otherwise I'll 
> just go back to jockeying around in the "Manage Recordings" menu 
> sub-tree to check on upcoming premieres and looking for additional 
> episodes getting added to the schedule.
>
> Thanks for the suggestions so far.  Additional advice welcome, as always.

You could just create another Custom Recording Rule that records all 
episodes of any titles that exist in the Premieres recording group...  
Something like

program.title in (SELECT DISTINCT(title) FROM recorded WHERE recgroup = 
'Premieres')

(There may well be much more efficient SQL to allow this...  Some kind 
of JOIN-based approach that avoids the subquery or ...  I'll let others 
who are better at SQL come up with improvements as desired.)

and, if you had that recording rule record to a the "Premieres" 
recording group, then when you see multiple episodes of a series in 
"Premieres", you can go and create a new recording rule for that 
series.  Then, just change the recording group of the already-recorded 
episodes to that used for the new series-specific rule (you can do this 
by putting all the episodes of the series in a playlist then using 
Playlist Options to change the recording group of all of them at once).

This has the benefit that it's self-maintaining until you get around to 
looking at the episodes, so you don't have a "have to check at least 
every 2 weeks or else I miss some episodes" problem.

Mike