Media List Editor Examples
#1
Information 
Please use this schema to post your SQLite example queries:



Movies with more than one audio stream
Type: movie
Query:
Code:
SELECT
  movielist.*
FROM
  movielist
  LEFT OUTER JOIN MoviesAStreams ON (movielist.idMovie = MoviesAStreams.MovieID)
GROUP BY
  movielist.idMovie
HAVING COUNT(MoviesAStreams.MovieID) > 1
Reply
#2
All 720p movies
Type: movie
Name: 720p movies
Query:
Code:
SELECT DISTINCT movielist.* FROM MoviesVStreams INNER JOIN movielist ON (MoviesVStreams.MovieID  = movielist.idMovie) WHERE MoviesVStreams.Video_Width = '1280'

All FullHD movies
Type: movie
Name: 1080p movies
Query:
Code:
SELECT DISTINCT movielist.* FROM MoviesVStreams INNER JOIN movielist ON (MoviesVStreams.MovieID  = movielist.idMovie) WHERE MoviesVStreams.Video_Width = '1920'

All non-HD movies
Type: movie
Name: not HD movies
Query:
Code:
SELECT DISTINCT movielist.* FROM MoviesVStreams INNER JOIN movielist ON (MoviesVStreams.MovieID  = movielist.idMovie) WHERE (MoviesVStreams.Video_Width != '1920' AND MoviesVStreams.Video_Width != '1280')

Anime movies
Type: movie
Name: Anime movies
Query:
Code:
SELECT * FROM movielist WHERE (Genre LIKE '%Animation%') AND (Country LIKE '%Japan%')

Movies with best audio quality
Type: movie
Name: Best Audio
Query:
Code:
SELECT DISTINCT movielist.* FROM MoviesAStreams INNER JOIN movielist ON (MoviesAStreams.MovieID  = movielist.idMovie) WHERE (MoviesAStreams.Audio_Codec = 'dtshd_hra' OR MoviesAStreams.Audio_Codec = 'dtshd_ma' OR MoviesAStreams.Audio_Codec = 'truehd')
Reply
#3
This coding is well beyond my capability. If someone wouldn't mind id love an example of how to do this for a specific source. Then I could separate my workout, documentaries, kids shows etc.. From my movies. If not possible then a specific genre or tag might work.
Reply
#4
(2015-11-27, 08:38)beesmyer Wrote: This coding is well beyond my capability. If someone wouldn't mind id love an example of how to do this for a specific source. Then I could separate my workout, documentaries, kids shows etc.. From my movies. If not possible then a specific genre or tag might work.

Example for a source called "Dokus":

Code:
SELECT
  movielist.*
FROM
  movielist
  INNER JOIN moviesource ON (movielist.idSource = moviesource.idSource)
WHERE
  moviesource.strName = "Dokus"
Reply
#5
Dan, Thank you for the help. I get an error when entering this. Can't figure out what I might be doing wrong. Any ideas? Or anything else I can give you to tell you the error?
Reply
#6
There is a log file inside the log folder. Please upload it as zip or something else.

Edit: Can you tell me your database version? .\EmberMEdiaManager\Settings\MyVideo??.emm
Maybe the moviesource table calls "Sources" in your version.
Reply
#7
MyVideos24

I'm searching rules and figuring out uploading log files right now since I haven't done it before
Reply
#8
Ok, in version 24 the movie source table is called "Sources", also the columns has other names. Please try this SQL:

Code:
SELECT
  movielist.*
FROM
  movielist
  INNER JOIN Sources ON (movielist.Source = Sources.Name)
WHERE
  Sources.Name = "Dokus"
Reply
#9
That disables the "add" button.

Honestly. I don't want this to take up much of your time. I can make do without and would much prefer some of the other work to get done. I know how much time it takes.
Reply
#10
(2015-11-29, 03:13)beesmyer Wrote: That disables the "add" button.

Honestly. I don't want this to take up much of your time. I can make do without and would much prefer some of the other work to get done. I know how much time it takes.

Oh, i think we have a little savety-check that does not work with newline between "SELECT" and "movielist".
Same code, but this should be working:

Code:
SELECT movielist.*
FROM movielist INNER JOIN Sources ON (movielist.Source = Sources.Name)
WHERE Sources.Name = "Dokus"
Reply
#11
That worked. Thank you. Now I'll try leave you alone so we can get the new release.
Reply
#12
I am just now getting around to playing with this and it's AWESOME! Unfortunately, I'm in the same boat as beesmyer....but I was able to get all of the examples given already working! My question is, how would I go about gaining a list that only shows hevc encodes? Again, I don't know what I'm doing, but would this work:

Code:
SELECT DISTINCT movielist.* FROM MoviesVCodecs INNER JOIN movielist ON (MoviesVCodecs.MovieID  = movielist.idMovie) WHERE MoviesVCodecs = 'hevc'

I just changed what looked like what needed to be changed, but I don't know this scripting Sad
Reply
#13
(2016-04-14, 04:20)Fail$tyle420 Wrote: I am just now getting around to playing with this and it's AWESOME! Unfortunately, I'm in the same boat as beesmyer....but I was able to get all of the examples given already working! My question is, how would I go about gaining a list that only shows hevc encodes?

Code:
SELECT DISTINCT
  movielist.*
FROM
  movielist
  INNER JOIN MoviesVStreams ON (movielist.idMovie = MoviesVStreams.MovieID)
WHERE
  MoviesVStreams.Video_Codec = 'hevc'
Reply
#14
(2016-04-14, 10:09)DanCooper Wrote:
(2016-04-14, 04:20)Fail$tyle420 Wrote: I am just now getting around to playing with this and it's AWESOME! Unfortunately, I'm in the same boat as beesmyer....but I was able to get all of the examples given already working! My question is, how would I go about gaining a list that only shows hevc encodes?

Code:
SELECT DISTINCT
  movielist.*
FROM
  movielist
  INNER JOIN MoviesVStreams ON (movielist.idMovie = MoviesVStreams.MovieID)
WHERE
  MoviesVStreams.Video_Codec = 'hevc'

Beautiful, thank you! I'll give this a go!
Reply
#15
EMM does not store the resolution of art files (poster, thumbs, fanart, etc), correct?
Reply



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