[mythtv-users] Power search help

[Karl Newman]

On Tue, Feb 18, 2014 at 7:15 PM, Hika van den Hoven <hikavdh at gmail.com>wrote:

>  Hoi Karl,
>
>
> Wednesday, February 19, 2014, 3:54:24 AM, you wrote:
>
>
>  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
>
>
> You're missing 'AS' before R2
>

I overlooked that one but it's optional.

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