[mythtv-users] Power search help

[Karl Newman]

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