[mythtv-users] Custom searches against the credits table?
Can this be done? For example, If I want to see all the clint east wood movies, I can create the following query from within Mysql, or on the command line... select people.name, channel.channum, credits.starttime, program.title from people, credits, program, channel where (program.chanid = channel.chanid) AND (credits.chanid = program.chanid) AND (credits.starttime = program.starttime) AND credits.person = people.person AND (people.name LIKE '%Clint Eastwood%') ORDER BY program.starttime; Works great. However, I can't use this in a custom query since it doesn't join against the credits table. Is this correct? Querying against the credits table would give better results, since the person's name is not always referenced in the description field of the program table. Any thoughts. Stewart ___ mythtv-users mailing list mythtv-users@mythtv.org http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
Re: [mythtv-users] Custom searches against the credits table?
Stewart wrote: Can this be done? For example, If I want to see all the clint east wood movies, I can create the following query from within Mysql, or on the command line... select people.name, channel.channum, credits.starttime, program.title from people, credits, program, channel where (program.chanid = channel.chanid) AND (credits.chanid = program.chanid) AND (credits.starttime = program.starttime) AND credits.person = people.person AND (people.name LIKE '%Clint Eastwood%') ORDER BY program.starttime; Works great. However, I can't use this in a custom query since it doesn't join against the credits table. Is this correct? The Custom Record page can't but kPowerSearch rules can... Querying against the credits table would give better results, since the person's name is not always referenced in the description field of the First to answer the question you want a solution for then to answer the question you asked =). You can go to Search Words-People and enter Clint Eastwood to search the name in people and find the credited programs. [Note: this uses people.name LIKE 'Clint Eastwood' with no wildcards by default for efficiency. However, it does use LIKE so you can enter %Eastwood or Qu%n Tar%tino if you were unsure of a spelling but exact names are more efficient.] If you see a list of spaghetti westerns, press M to bring up the popup, Tab or M again to focus on the buttons and click Record. The title on the recording options page should be Clint Eastwood (People Search). [Hint: If you set an All rule, it may record a dozen in the first week. In SVN a couple months ago, fixes were made to be sure that kFindWeeklyRecord worked correctly for search rules that match different titles. Therefore, you can choose Record one showing of this title every week. This will choose one Clint Eastwood film to record each week. Once a film has recorded, future showings will be marked P so it will choose a different Eastwood film each week.] Now, to the question you asked. Search rules put the key info for the WHERE clause in the record.description fields. The record.subtitle field can also hold a string to go into the FROM clause. This is precisely for People Search rules and is the only thing that currently uses this. However, it might be useful to join other table like oldprogram to determine if a title has been in the listings before like the New Titles list. kPowerSearch rules do support a fromclause string in the subtitle but the Custom Record page doesn't support this because it it too obscure and too complex for a simple interface. The Custom Record page and the proglist pages are not prepared to handle fromclauses and would choke badly on power search rules that had them. This should be fixed but has never come up. If you decide to do something with fromclauses, please send a patch for Custom Record to handle them. -- bjm ___ mythtv-users mailing list mythtv-users@mythtv.org http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
Re: [mythtv-users] Custom searches against the credits table?
Thanks a bunch. Exactly what I needed. Stewart Larsen It is the first responsibility of every citizen to question authority. ~ Ben Franklin Hackers do for love what others wouldn't do for money. If you think technology will solve your security problems, you don't understand the technology and you don't understand the problems. ~Bruce Schneier Bruce Markey wrote: Stewart wrote: Can this be done? For example, If I want to see all the clint east wood movies, I can create the following query from within Mysql, or on the command line... select people.name, channel.channum, credits.starttime, program.title from people, credits, program, channel where (program.chanid = channel.chanid) AND (credits.chanid = program.chanid) AND (credits.starttime = program.starttime) AND credits.person = people.person AND (people.name LIKE '%Clint Eastwood%') ORDER BY program.starttime; Works great. However, I can't use this in a custom query since it doesn't join against the credits table. Is this correct? The Custom Record page can't but kPowerSearch rules can... Querying against the credits table would give better results, since the person's name is not always referenced in the description field of the First to answer the question you want a solution for then to answer the question you asked =). You can go to Search Words-People and enter Clint Eastwood to search the name in people and find the credited programs. [Note: this uses people.name LIKE 'Clint Eastwood' with no wildcards by default for efficiency. However, it does use LIKE so you can enter %Eastwood or Qu%n Tar%tino if you were unsure of a spelling but exact names are more efficient.] If you see a list of spaghetti westerns, press M to bring up the popup, Tab or M again to focus on the buttons and click Record. The title on the recording options page should be Clint Eastwood (People Search). [Hint: If you set an All rule, it may record a dozen in the first week. In SVN a couple months ago, fixes were made to be sure that kFindWeeklyRecord worked correctly for search rules that match different titles. Therefore, you can choose Record one showing of this title every week. This will choose one Clint Eastwood film to record each week. Once a film has recorded, future showings will be marked P so it will choose a different Eastwood film each week.] Now, to the question you asked. Search rules put the key info for the WHERE clause in the record.description fields. The record.subtitle field can also hold a string to go into the FROM clause. This is precisely for People Search rules and is the only thing that currently uses this. However, it might be useful to join other table like oldprogram to determine if a title has been in the listings before like the New Titles list. kPowerSearch rules do support a fromclause string in the subtitle but the Custom Record page doesn't support this because it it too obscure and too complex for a simple interface. The Custom Record page and the proglist pages are not prepared to handle fromclauses and would choke badly on power search rules that had them. This should be fixed but has never come up. If you decide to do something with fromclauses, please send a patch for Custom Record to handle them. -- bjm ___ mythtv-users mailing list mythtv-users@mythtv.org http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users ___ mythtv-users mailing list mythtv-users@mythtv.org http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
