• 1
  • 2
  • 3(current)
  • 4
  • 5
  • 7
Media List Editor Examples
#31
(2017-08-30, 12:58)PH-SYM Wrote: Works great.
BTW, is there a specific reason for using "SELECT DISTINCT" instead of just "SELECT"? Just to check whether I understand it correctly, it will filter out duplicate movies won't it?

You can use "SELECT DISTINCT" for all cases, but it's not always needed. It's needed if you e.g. multiple linked tables like "show my all movies that have video codec x264 or audio codec mp3". In this case a movie can by listed for case one and case two.
Reply
#32
(2017-11-09, 01:08)calexicoca Wrote: Just wondering if someone can help. It's beyond my knowledge to create a custom list. I would like to have a list of TV Shows when it was premiered and a separate one when was it added to database? Is that complicated to make?

Do you wanna show this columns in the list or ordering by that? I don know what you want.
Reply
#33
(2017-11-09, 10:36)DanCooper Wrote:
(2017-11-09, 01:08)calexicoca Wrote: Just wondering if someone can help. It's beyond my knowledge to create a custom list. I would like to have a list of TV Shows when it was premiered and a separate one when was it added to database? Is that complicated to make?

Do you wanna show this columns in the list or ordering by that? I don know what you want. 
Both if possible. If not than just to be able to order by these two.
Reply
#34
(2017-11-09, 13:38)calexicoca Wrote:
(2017-11-09, 10:36)DanCooper Wrote:
(2017-11-09, 01:08)calexicoca Wrote: Just wondering if someone can help. It's beyond my knowledge to create a custom list. I would like to have a list of TV Shows when it was premiered and a separate one when was it added to database? Is that complicated to make?

Do you wanna show this columns in the list or ordering by that? I don know what you want.  
Both if possible. If not than just to be able to order by these two. 

DataAdded will be saved as UnixTimeStamp. That's not a readable value and make no sense to add it to the "available columns" list to hide or show. You can sort the list by AddingDate using the button in the filter panel.
Premiered/Release Date will be added soon to the media list.

The Media List Edit can't add or remove columns, it's only to create own SQL queries to filter the list.
Reply
#35
I did what you've suggested. Added "AddingDate" in Media list. Ember rescanned all shows and now I do not know where to find this to show this new sorting option. It is not in the any available filters and modifiers? Am I missing something or I am just plain stupid?
Reply
#36
(2017-11-10, 14:59)calexicoca Wrote: I did what you've suggested. Added "AddingDate" in Media list. Ember rescanned all shows and now I do not know where to find this to show this new sorting option. It is not in the any available filters and modifiers? Am I missing something or I am just plain stupid?

Hehe, tv shows don't have an "DateAdded" field, only movies and episodes. I've to add a new column to the tvshow view that get the highest episode "DateAdded" value, then I can add a new button to sort the tv shows by "newest added episodes per tv show". Has been added to the Feature Request list: Link
Reply
#37
I see. Thanks!
Reply
#38
(2015-06-11, 00:18)Cocotus Wrote: 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')
I just started to add UltraHD movies to my collection and then I tried to update the non-hd movie query as below, but its not working Sad
sql:
SELECT DISTINCT movielist.* FROM MoviesVStreams INNER JOIN movielist ON (MoviesVStreams.MovieID  = movielist.idMovie) WHERE (MoviesVStreams.Video_Width != '2160' AND MoviesVStreams.Video_Width != '1920' AND MoviesVStreams.Video_Width != '1280')
Reply
#39
(2017-11-19, 14:50)shwetkprabhat Wrote: I just started to add UltraHD movies to my collection and then I tried to update the non-hd movie query as below, but its not working Sad
sql:
SELECT DISTINCT movielist.* FROM MoviesVStreams INNER JOIN movielist ON (MoviesVStreams.MovieID  = movielist.idMovie) WHERE (MoviesVStreams.Video_Width != '2160' AND MoviesVStreams.Video_Width != '1920' AND MoviesVStreams.Video_Width != '1280')
 
2160 is the height not the width
Reply
#40
(2017-11-19, 15:10)komplex Wrote:
(2017-11-19, 14:50)shwetkprabhat Wrote: I just started to add UltraHD movies to my collection and then I tried to update the non-hd movie query as below, but its not working Sad
sql:
SELECT DISTINCT movielist.* FROM MoviesVStreams INNER JOIN movielist ON (MoviesVStreams.MovieID  = movielist.idMovie) WHERE (MoviesVStreams.Video_Width != '2160' AND MoviesVStreams.Video_Width != '1920' AND MoviesVStreams.Video_Width != '1280')
2160 is the height not the width 
 Thanks, yes I just figured that out came to update, appreciate the quick response!
Reply
#41
Looking for some assistance to query all movies from a particular path.  \\servername\folder\subfolder1

Help is appreciated.  thanks.
Reply
#42
(2017-12-16, 23:22)Toxic Man Wrote: Looking for some assistance to query all movies from a particular path.  \\servername\folder\subfolder1

Help is appreciated.  thanks.
  
sql:
SELECT * FROM movielist WHERE movielist.MoviePath LIKE "\\servername\folder\subfolder1%"
Reply
#43
(2017-12-17, 18:49)DanCooper Wrote: SELECT * FROM movielist WHERE movielist.MoviePath LIKE "\\servername\folder\subfolder1%"

Excellent.  This works, but is picking up movies that are also in \\servername\folder\subfolder2

Is that because of the operator LIKE being used in this query?
Reply
#44
(2017-12-18, 03:12)Toxic Man Wrote:
(2017-12-17, 18:49)DanCooper Wrote: SELECT * FROM movielist WHERE movielist.MoviePath LIKE "\\servername\folder\subfolder1%"

Excellent.  This works, but is picking up movies that are also in \\servername\folder\subfolder2

Is that because of the operator LIKE being used in this query?  
LIKE is needed if you don't search by an exactly value. The "MoviePath" is always the full path a media file, so you have to use LIKE to list more than one movie. The % is equal to the * in a Windows Explorer search. So if your first path is "\\SERVER\Movies\Blockbusters" and your second one is "\\SERVER\Movies\Blockbusters from India" then my query will list boths.

I don't know the real paths you want to filter, so it's a little bit difficult to say what's the correct query for you.
Reply
#45
(2017-12-18, 10:39)DanCooper Wrote:
(2017-12-18, 03:12)Toxic Man Wrote:
(2017-12-17, 18:49)DanCooper Wrote: SELECT * FROM movielist WHERE movielist.MoviePath LIKE "\\servername\folder\subfolder1%"

Excellent.  This works, but is picking up movies that are also in \\servername\folder\subfolder2

Is that because of the operator LIKE being used in this query?     
LIKE is needed if you don't search by an exactly value. The "MoviePath" is always the full path a media file, so you have to use LIKE to list more than one movie. The % is equal to the * in a Windows Explorer search. So if your first path is "\\SERVER\Movies\Blockbusters" and your second one is "\\SERVER\Movies\Blockbusters from India" then my query will list boths.

I don't know the real paths you want to filter, so it's a little bit difficult to say what's the correct query for you.   
sql:
SELECT * FROM movielist WHERE movielist.MoviePath LIKE "\\servername\folder\subfolder1\%"
should work. Notice the additional \ before the %.
Reply
  • 1
  • 2
  • 3(current)
  • 4
  • 5
  • 7

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