Media List Editor Examples

  Thread Rating:
  • 1 Vote(s) - 5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Post Reply
DanCooper Online
Moderator
Posts: 3,383
Joined: Apr 2012
Reputation: 167
Location: Switzerland
Information  Media List Editor Examples
Post: #1
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
(This post was last modified: 2015-06-11 11:14 by DanCooper.)
find quote
Cocotus Offline
Moderator
Posts: 453
Joined: Mar 2011
Reputation: 13
Location: Germany
Post: #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')
(This post was last modified: 2015-06-11 00:18 by Cocotus.)
find quote
beesmyer Offline
Member
Posts: 70
Joined: Nov 2015
Reputation: 0
Post: #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.
find quote
DanCooper Online
Moderator
Posts: 3,383
Joined: Apr 2012
Reputation: 167
Location: Switzerland
Post: #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"
find quote
beesmyer Offline
Member
Posts: 70
Joined: Nov 2015
Reputation: 0
Post: #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?
find quote
DanCooper Online
Moderator
Posts: 3,383
Joined: Apr 2012
Reputation: 167
Location: Switzerland
Post: #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.
(This post was last modified: 2015-11-28 14:34 by DanCooper.)
find quote
beesmyer Offline
Member
Posts: 70
Joined: Nov 2015
Reputation: 0
Post: #7
MyVideos24

I'm searching rules and figuring out uploading log files right now since I haven't done it before
find quote
DanCooper Online
Moderator
Posts: 3,383
Joined: Apr 2012
Reputation: 167
Location: Switzerland
Post: #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"
(This post was last modified: 2015-11-29 00:17 by DanCooper.)
find quote
beesmyer Offline
Member
Posts: 70
Joined: Nov 2015
Reputation: 0
Post: #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.
find quote
DanCooper Online
Moderator
Posts: 3,383
Joined: Apr 2012
Reputation: 167
Location: Switzerland
Post: #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"
find quote
beesmyer Offline
Member
Posts: 70
Joined: Nov 2015
Reputation: 0
Post: #11
That worked. Thank you. Now I'll try leave you alone so we can get the new release.
find quote
Fail$tyle420 Offline
Posting Freak
Posts: 1,188
Joined: Jul 2014
Reputation: 43
Post: #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
find quote
DanCooper Online
Moderator
Posts: 3,383
Joined: Apr 2012
Reputation: 167
Location: Switzerland
Post: #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'
find quote
Fail$tyle420 Offline
Posting Freak
Posts: 1,188
Joined: Jul 2014
Reputation: 43
Post: #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!
find quote
Daydream Offline
Junior Member
Posts: 26
Joined: Jul 2009
Reputation: 0
Location: New York
Post: #15
EMM does not store the resolution of art files (poster, thumbs, fanart, etc), correct?
find quote
Post Reply