Media List Editor Examples
#61
can someone help me with some querys?

1. find movie plots with specific language (or maybe find a word like "the" in the plot?)

2. find movies with more than 3 genres

3. find movies with video bitrate < 10000

4. find movies with filesize > 10000 mb
Reply
#62
(2018-03-15, 00:36)archiv Wrote: can someone help me with some querys?

1. find movie plots with specific language (or maybe find a word like "the" in the plot?)

2. find movies with more than 3 genres

3. find movies with video bitrate < 10000

4. find movies with filesize > 10000 mb

1.
SELECT DISTINCT * FROM movielist WHERE Plot LIKE '%the%'

2.
SELECT DISTINCT
  movielist.*
FROM
  genre
  LEFT OUTER JOIN genrelinkmovie ON (genre.idGenre = genrelinkmovie.idGenre)
  LEFT OUTER JOIN movielist ON (genrelinkmovie.idMovie = movielist.idMovie)
GROUP BY
  movielist.idMovie
HAVING
  COUNT(genre.idGenre) > 3

3.
SELECT DISTINCT
  movielist.*
FROM
  movielist
  INNER JOIN MoviesVStreams ON (movielist.idMovie = MoviesVStreams.MovieID)
WHERE
  MoviesVStreams.Video_Bitrate < 10000

4. not possible, filesize is not stored to the database
Reply
#63
Movies with chinese subtitles
Database version: MyVideos46.emm
Type: movie
Query:
SELECT DISTINCT
  movielist.*
FROM
  movielist
  INNER JOIN MoviesSubs ON (movielist.idMovie = MoviesSubs.MovieID)
WHERE
  MoviesSubs.Subs_LongLanguage LIKE 'chinese'

Movies with chinese embedded subtitles
Database version: MyVideos46.emm
Type: movie
Query:
SELECT DISTINCT
  movielist.*
FROM
  movielist
  INNER JOIN MoviesSubs ON (movielist.idMovie = MoviesSubs.MovieID)
WHERE
  MoviesSubs.Subs_LongLanguage LIKE 'chinese' AND
  MoviesSubs.Subs_Type = 'Embedded'

Movies with chinese external subtitles (files)
Database version: MyVideos46.emm
Type: movie
Query:
SELECT DISTINCT
  movielist.*
FROM
  movielist
  INNER JOIN MoviesSubs ON (movielist.idMovie = MoviesSubs.MovieID)
WHERE
  MoviesSubs.Subs_LongLanguage LIKE 'chinese' AND
  MoviesSubs.Subs_Type = 'External'
Reply
#64
I made a simple modification to an example query to find all non-HD movies but it doesn't seem to work:
SELECT DISTINCT movielist.* FROM MoviesVStreams INNER JOIN movielist ON (MoviesVStreams.MovieID  = movielist.idMovie)
WHERE (MoviesVStreams.Video_Width < 900)
Is MoviesVStreams.Video_Width a string type and not a number type, which is why the examples use quotes around the number (eg: '1280', '1080').
Reply
#65
Fix: ...WHERE (CAST (MoviesVStreams.Video_Width as UNSIGNED int) < 900)
Reply
#66
@DanCooper how can I delete some custom media query lists.
Image Image
Reply
#67
(2019-07-29, 22:52)redglory Wrote: @DanCooper how can I delete some custom media query lists.

Select it in the settings upper dropdown list and use the remove button:

Image
Reply
#68
(2019-07-30, 00:18)DanCooper Wrote:
(2019-07-29, 22:52)redglory Wrote: @DanCooper how can I delete some custom media query lists.

Select it in the settings upper dropdown list and use the remove button:

Image
Hi Dan mine is greyed out when I select one on the list.

Thx
Image Image
Reply
#69
(2019-07-30, 10:07)redglory Wrote: Hi Dan mine is greyed out when I select one on the list.

Thx

Hmmm, i think the button is disabled if the "title" field is empty or the SQL query is wrong. Try this:
  1. select the list in the dropdown menu
  2. fill a title if empty
  3. use "SELECT * FROM movielist" as query, than the remove button should be enabled
Reply
#70
(2019-07-30, 11:33)DanCooper Wrote:
(2019-07-30, 10:07)redglory Wrote: Hi Dan mine is greyed out when I select one on the list.

Thx

Hmmm, i think the button is disabled if the "title" field is empty or the SQL query is wrong. Try this:
  1. select the list in the dropdown menu
  2. fill a title if empty
  3. use "SELECT * FROM movielist" as query, than the remove button should be enabled
 

Had already tried every combination. Still greyed out
Image

EDIT: tried to select sets on type added pt onde the title and remove appeared. Clicked it and it removed my saved query.

A little weird but it worked.

EDIT2: It makes sense. Titles are preceeded with type so you only have to put the title after the hypen for it to be selected. Smile
Image Image
Reply
#71
Hi - is the mppa rating saved in database and how to create list with R or NC-17 rating if possible?
Reply
#72
(2020-04-07, 21:02)geek-baba Wrote: Hi - is the mppa rating saved in database and how to create list with R or NC-17 rating if possible?
SELECT *
FROM
  movielist
WHERE
  MPAA LIKE 'R%' OR
  MPAA LIKE 'NC-17%'
Reply
#73
(2020-04-07, 22:48)DanCooper Wrote:
(2020-04-07, 21:02)geek-baba Wrote: Hi - is the mppa rating saved in database and how to create list with R or NC-17 rating if possible?
SELECT *
FROM
  movielist
WHERE
  MPAA LIKE 'R%' OR
  MPAA LIKE 'NC-17%'
 
Thank you! Figured certification field works better, because MPPA field has "Rated R" so above query will fetch almost 99% of the movies.
SELECT *
FROM
  movielist
WHERE
  Certification LIKE 'USA:R%'
Reply
#74
(2020-04-08, 01:18)geek-baba Wrote: Thank you! Figured certification field works better, because MPPA field has "Rated R" so above query will fetch almost 99% of the movies.
The % char in the query is the same like * in Windows. That's the reason why "every rating starting with "r" will be listed.

If you one want to search for "Rated R" than you can use
SELECT * FROM movielist WHERE MPAA LIKE 'Rated R'
or
SELECT * FROM movielist WHERE Certification LIKE 'USA:R'
Reply
#75
Hi there looking for some queries concerning audio stream languages. Thanks in advance! Smile

1. Movies with only a German audio stream

2. Movies with a German AND a English (or any other language) audio stream

3. Movies with only 1 audio stream

4. Movies with more than 1 audio stream
Reply



Logout Mark Read Team Forum Stats Members Help
Media List Editor Examples1
This forum uses Lukasz Tkacz MyBB addons.