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
German Ember Media Manager Support

Daily Builds - Link
Latest official Ember-MM version - Ember Media Manager 1.4.x
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'
German Ember Media Manager Support

Daily Builds - Link
Latest official Ember-MM version - Ember Media Manager 1.4.x
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
 
Thread Rating:
  • 1 Vote(s) - 5 Average



Logout Mark Read Team Forum Stats Members Help
Media List Editor Examples51