[mythtv-users] Power search help

[Karl Newman]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.mythtv.org/pipermail/mythtv-users/attachments/20140218/64b117f1/attachment.html>