JeF's Extra Queries (for interface and/or plugin)

How to make Movienizer support new sites. Customize Movienizer.
User avatar
Vlad
Developer
Posts: 2969
Joined: Wed Jan 23, 2008 11:37 am

Re: JeF's Extra Queries (for interface and/or plugin)

Post by Vlad » Sun Jun 26, 2011 7:17 am

If for some reason certain movies are not "In collection" even though they are linked to video files (and thus obviously you do have them), this query can help:

Code: Select all

M0107=UPDATE movies SET in_collection=1 WHERE code IN (SELECT movie FROM editions WHERE filename<>'')
M0107D=Set the In collection flag for all movies that have links to files
Edited by JeF : Thanks Vald, query added to first post with id 0128
ImageImage

GOMAS73
Passerby
Posts: 5
Joined: Thu Sep 29, 2011 4:36 am

Re: JeF's Extra Queries (for interface and/or plugin)

Post by GOMAS73 » Thu Sep 29, 2011 4:41 am

Hi, I need a help:

I'd like to update my movie database, when I have a movie running a query in order to update the field "Wanted" (untick)

Can you help me?

Thanks!

User avatar
JeF
Black Belt Squirrel
Posts: 1175
Joined: Fri Feb 20, 2009 3:34 pm
Location: France

Re: JeF's Extra Queries (for interface and/or plugin)

Post by JeF » Thu Sep 29, 2011 6:41 am

Hi,
GOMAS73 wrote:Hi, I need a help: I'd like to update my movie database, when I have a movie running a query in order to update the field "Wanted" (untick)
Could you give som details about the conditions to untick Wanted flag ?
Is it for movies previously wanted without downloaded infos or not in collection and which are now in collection (so in collection flag could be used to untick 'wanted' flag) ?
Is it for movies you've now seen (playing file will automatically set the 'seen' flag so wa can use it to untick 'wanted' flag ?
Other cases ?

Here is the query for previously cases :
Switch off 'Wanted' for movies which are 'in collection' or 'seen' ( Query added to first post with id 0129)
(in plugin, click import button and copy paste those lines)

Code: Select all

M0129=UPDATE movies SET wanted = 0 WHERE wanted = 1 AND ( in_collection = 1 OR seen = 1)
M0129D=Switch off 'Wanted' flag for movies 'in collection' or 'seen'
Please let me know if it's what you're expecting. Otherwise, i'll post another query with the new rules you'll mention.

;)
JeF

alpa
Supporting actor
Posts: 91
Joined: Sat Aug 02, 2008 10:25 am

Re: JeF's Extra Queries (for interface and/or plugin)

Post by alpa » Fri Nov 11, 2011 9:23 am

Hi, is it possible for a plugin for "all actors without a photo", thanks

Image

Uploaded with ImageShack.us

User avatar
JeF
Black Belt Squirrel
Posts: 1175
Joined: Fri Feb 20, 2009 3:34 pm
Location: France

Re: JeF's Extra Queries (for interface and/or plugin)

Post by JeF » Fri Nov 11, 2011 10:14 am

alpa wrote:Hi, is it possible for a plugin for "all actors without a photo", thanks
Here it is (also added to first post)

Code: Select all

P0131=select code, name from persons where in_collection=1 and code not in (select distinct person from images where person > 0 and mode = 6) order by date_add desc, code desc
P0131D=Persons in collection without a Photo (by creation order, newest first)
See plugin post for 'how to' add that query to the plugin
;)
JeF

alpa
Supporting actor
Posts: 91
Joined: Sat Aug 02, 2008 10:25 am

Re: JeF's Extra Queries (for interface and/or plugin)

Post by alpa » Fri Nov 11, 2011 11:31 am

thanks a lot Jef (but I mean for all the actors in the database)

User avatar
JeF
Black Belt Squirrel
Posts: 1175
Joined: Fri Feb 20, 2009 3:34 pm
Location: France

Re: JeF's Extra Queries (for interface and/or plugin)

Post by JeF » Fri Nov 11, 2011 4:51 pm

alpa wrote:thanks a lot Jef
You're welcome
(but I mean for all the actors in the database)
You mean you'll manually add a photo for each person of the database ? :o
Then just erase [ in_collection=1 and ] from the query command. so this will look like

Code: Select all

select code, name from persons where code not in (select distinct person from images where person > 0 and mode = 6) order by date_add desc, code desc
;)
JeF

alpa
Supporting actor
Posts: 91
Joined: Sat Aug 02, 2008 10:25 am

Re: JeF's Extra Queries (for interface and/or plugin)

Post by alpa » Sat Nov 12, 2011 1:21 pm

Waaauw :o , Thats it, thanks a lot Jef, grt ;)

GOMAS73
Passerby
Posts: 5
Joined: Thu Sep 29, 2011 4:36 am

Re: JeF's Extra Queries (for interface and/or plugin)

Post by GOMAS73 » Mon Nov 14, 2011 3:19 am

Can you help with a query that list how movies seen by actors?

Thanks so much!

User avatar
JeF
Black Belt Squirrel
Posts: 1175
Joined: Fri Feb 20, 2009 3:34 pm
Location: France

Re: JeF's Extra Queries (for interface and/or plugin)

Post by JeF » Mon Nov 14, 2011 10:30 am

Hi,
GOMAS73 wrote:Can you help with a query that list how movies seen by actors?
As i really did find the idea interesting, i added some extra counts : movies as actor, in collection movies and the requested seen count
So, here's the query (also added to first post) (for plugin use)

Code: Select all

P0135=SELECT p.code as Code, p.name as Name, COUNT( d.person ) as RoleCount, COUNT( m2.code) as CollectionCount, COUNT( m.code) as SeenCount FROM data d, persons p LEFT OUTER JOIN movies m ON m.code = d.movie AND m.seen = 1 LEFT OUTER JOIN movies m2 ON m2.code = d.movie AND m2.in_collection = 1 WHERE d.person = p.code AND d.mode = 3 GROUP BY p.code ORDER BY COUNT( d.person ) DESC, p.name ASC
P0135D=Movie Counts by Actor : Movies as actor, In collection movies, Seen movies
;)
JeF

Post Reply