[mythtv-users] Power Search Category oddness

[Bruce Markey]

First, I apology for being a bit giddy. After finally deciding
to do something even more obscure with Custom Record, it pays
off a few days later. Let's first establish that you really are
a different person an not me posting under alias just so I can
show off, right?

Steve Adeff wrote:
> I'm trying to make a power search to match what shows as "Category: Sports 
> event, Soccer" in the program details. Some have ", Playoff sports" or other 
> things after this.

[Dang. This will only work with DD and if he is interested in
Soccer he's not in North American. Wait, he didn't say "Futbal",
he said "Soccer". He's one of us... an ugly American! ;-]
 
> So one would think
> 
> program.category LIKE '%Soccer%' would work. it doesn't.

The genres/categories hold specific stings and it is a magnitude
faster to do "= 'Soccer'" It may be a good to do something like
LIKE 'Sports %event' to match 'event' and 'non-event' but generally
use exact stings.

> program.category LIKE '%Sports event%' shows all sports events, so it looks 
> like Category does contain this much, but what field holds the type of sport? 
> I see events show the type of sport (Golf, Football, Bicycle racing, Tennis, 
> etc) so it must be held somewhere, how do I search for it?!

Data Direct include a list of categories or genres:

mysql> describe programgenres;
+-----------+------------------+------+-----+---------------------+-------+
| Field     | Type             | Null | Key | Default             | Extra |
+-----------+------------------+------+-----+---------------------+-------+
| chanid    | int(10) unsigned |      | PRI | 0                   |       |
| starttime | datetime         |      | PRI | 0000-00-00 00:00:00 |       |
| relevance | char(1)          |      | PRI |                     |       |
| genre     | char(30)         | YES  |     | NULL                |       |
+-----------+------------------+------+-----+---------------------+-------+
4 rows in set (0.00 sec)

The "relevance" is the ordering of the genre that best describes the
show. "0" is the best fit, "1" is the next best and so on:

mysql> select chanid,starttime,category,title from program where title like 'American Experience';
+--------+---------------------+-----------+---------------------+
| chanid | starttime           | category  | title               |
+--------+---------------------+-----------+---------------------+
|   1010 | 2005-11-06 00:00:00 | Anthology | American Experience |
...

mysql> select * from programgenres where chanid=1010 and starttime='2005-11-0600:00:00';
+--------+---------------------+-----------+-------------+
| chanid | starttime           | relevance | genre       |
+--------+---------------------+-----------+-------------+
|   1010 | 2005-11-06 00:00:00 | 0         | Anthology   |
|   1010 | 2005-11-06 00:00:00 | 1         | Documentary |
|   1010 | 2005-11-06 00:00:00 | 2         | Biography   |
|   1010 | 2005-11-06 00:00:00 | 3         | History     |
+--------+---------------------+-----------+-------------+

The program has one field for 'category' so we fill that in with
the zero-th genre (see "Anthology" above).

About two or three weeks ago, I added the additional genres on the
program details page. In the past, this would have said:

  Category: Anthology

but with current SVN it says:

  Category: Anthology, Documentary, Biography, History


The issue you are facing is that "Soccer" is buried in the lower
relevance genre:

mysql> select chanid,starttime,category,title from program where title = 'EPL Preview';
+--------+---------------------+------------------+-------------+
| chanid | starttime           | category         | title       |
+--------+---------------------+------------------+-------------+
|   2320 | 2005-11-11 15:30:00 | Sports non-event | EPL Preview |
...

mysql> select * from programgenres where chanid=2320 and starttime='2005-11-1115:30:00';
+--------+---------------------+-----------+------------------+
| chanid | starttime           | relevance | genre            |
+--------+---------------------+-----------+------------------+
|   2320 | 2005-11-11 15:30:00 | 0         | Sports non-event |
|   2320 | 2005-11-11 15:30:00 | 1         | Soccer           |
+--------+---------------------+-----------+------------------+
2 rows in set (0.00 sec)

In order to find all match for "Soccer", we must JOIN the table
for programgenres. See: http://cvs.mythtv.org/trac/changeset/7666 .
Power Search has always had the ability to include other tables
and just this Tuesday I finally took the plunge and added this
even more obscure capability to Custom Record.

With post Halloween evil 7"666" SVN or later, go to Custom Record
and select the new example "All matches for a genre (Data Direct)".
Replace 'Reality' with 'Soccer' then press "Test". I get over one
hundred results mostly on FSWLD Fox Sports World. If I do a category
search for "Soccer" I get no matches.

--  bjm