[mythtv-users] Power search help

[Karl Newman]

On Tue, Feb 18, 2014 at 6:42 PM, Karl Newman <newmank1 at asme.org> wrote:

> On Tue, Feb 18, 2014 at 6:25 PM, Hika van den Hoven <hikavdh at gmail.com>wrote:
>
>>  Hoi Hika,
>>
>>
>> Wednesday, February 19, 2014, 3:15:37 AM, you wrote:
>>
>>
>>  Hoi Karl,
>>
>> Wednesday, February 19, 2014, 2:54:57 AM, you wrote:
>>
>>
>>  On Tue, Feb 18, 2014 at 5:46 PM, Hika van den Hoven <hikavdh at gmail.com>
>> wrote:
>> Hoi Karl,
>>
>>
>> Wednesday, February 19, 2014, 2:30:18 AM, you wrote:
>>
>>
>>  I need some help from the SQL experts which I know lurk on this list.
>> I'm trying to create a power rule to re-record movies (which are currently
>> recorded) when they air on channels which I've marked as commercial free. I
>> started with the re-record in HD rule as per
>> http://www.mythtv.org/wiki/Custom_Recording#Recording_a_Rebroadcast_in_HD:_An_Advanced_Custom_Recording_Example and
>> have the basic search working like this (formatted as a SQL query for
>> testing, not as a power rule--ignore the title, starttime, channel.name
>>  columns):
>>
>> SELECT program.title, program.starttime, channel.name FROM program JOIN
>> channel ON (program.chanid = channel.chanid) JOIN recordedprogram AS R
>> USING (programid) JOIN channel AS C ON (R.chanid = C.chanid AND
>> C.commmethod != -2) WHERE program.category_type = "movie";
>>
>> It's missing the "AND channel.commmethod = -2" clause to only pick up
>> programs airing on comm free channels because I'm planning to just use the
>> "Commercial Free" filter which I presume will be appended to the WHERE
>> clause. I probably also need to add filters for Live TV and Deleted groups,
>> but that's pretty trivial to add. This seems to work great, but the problem
>> is that I have to disable duplicate matching for this to work, obviously,
>> so I need to implement that in the SELECT statement otherwise it will keep
>> recording the movie as long as the non-commercial free recording exists.
>> Following the pattern in the HD re-record I tried to do this:
>>
>> SELECT program.title, program.starttime, channel.name FROM program JOIN
>> channel ON (program.chanid = channel.chanid) JOIN recordedprogram AS R
>> USING (programid) JOIN channel AS C ON (R.chanid = C.chanid AND
>> C.commmethod != -2) LEFT OUTER JOIN recordedprogram R2 USING (programid)
>> JOIN channel AS C2 ON (R2.chanid = C2.chanid AND C2.commmethod = -2) WHERE
>> program.category_type = "movie" AND R2.starttime = NULL ORDER BY title;
>> The idea there is to exclude items where there is an entry for that
>> programid in recordedprogram which was recorded on a commercial free
>> channel (C2.commmethod = -2). The problem is to accomplish that I think I
>> need to do an inner join of the channel table ONLY on/with the chanid from
>> the outer joined R2 and I don't know how to structure that. The above
>> almost works but I think it's joining C2 on the entire previous selected
>> row which causes it to return an empty set (because then there are clauses
>> for commmethod = -2 AND commmethod != -2).
>>
>> So, any suggestions?
>>
>> Thanks,
>>
>> Karl
>>
>>
>>
>>
>> You can try nested queries, which means doing a guery on a query instead
>> of on a table.
>> I'm not exactly clear on the syntax and whether mysql supports this. This
>> was long ago
>> on an Oracle database.
>> Thanks. I played with nested queries a little bit while following an
>> alternate path to duplicate matching, but my impression is that mysql
>> performance can be variable with nested queries, and also I think it's more
>> than I need. This query is very fast (0.00 sec--probably just hitting cache
>> though) and I think I'm very close to a solution, I just need to know how
>> to structure that one join.
>>
>> Karl
>>
>>
>>
>> I think you're encountering a problem I have encountered with joins. If
>> any side of it gives no record you get empty.
>> Even though with your own logic you would expect result.
>> I probably am phrasing it badly. But the solution for me was to work with
>> two independent queries or filter further
>> in programming code. I think, but I'm only a self-learned in sql, the
>> nested solution is your best bet.
>>
>> Tot mails,
>>   Hika                            mailto:hikavdh at gmail.com<hikavdh at gmail.com>
>>
>> "Zonder hoop kun je niet leven
>> Zonder leven is er geen hoop
>> Het eeuwige dilemma
>> Zeker als je hoop moet vernietigen om te kunnen overleven!"
>>
>> De lerende Mens
>> --
>>
>>
>> I'm thinking about my phrasing; I mean if either part has no record that
>> satisfies the join,
>> it doesn't look any further but throws it out. So any possible satisfying
>> record that doesn't
>> exist in any member of the join...
>>
>
> I think I found the answer. My earlier experiments with parentheses
> resulted in syntax errors but now that I look closer at it I think I
> understand why. Here's what I have now:
> SELECT program.title, program.starttime, channel.name FROM program JOIN
> channel ON (program.chanid = channel.chanid) JOIN recordedprogram AS R
> USING (programid) JOIN channel AS C ON (R.chanid = C.chanid AND
> C.commmethod != -2) LEFT OUTER JOIN (recordedprogram R2 JOIN channel AS C2
> ON (R2.chanid = C2.chanid AND C2.commmethod = -2)) USING (programid) WHERE
> program.category_type = "movie" AND R2.starttime = NULL;
>
> I'm still getting an empty set, though, even when I temporarily mark a
> channel with upcoming re-airings as commercial free. I need to experiment
> some more. I think maybe I need to give my outer joined table a name and
> then check that starttime = NULL on that.
>
> Karl
>

Okay, this is weird. If I remove the AND R2.starttime = NULL clause, then I
get the results that I was getting before I added the duplicate matching
statements. However, if I add R2.starttime to the SELECTed columns, they
all show up as NULL. So why isn't that matching when it's in the WHERE
clause???

Karl
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.mythtv.org/pipermail/mythtv-users/attachments/20140218/0e809add/attachment.html>