LMH need help! Help plz ... How select episodes in Online Rating sort order | Page 2

Discussion in 'My TVSeries' started by ajs, January 26, 2018.

  1. ltfearme
    • Premium Supporter

    ltfearme Community Plugin Dev

    Joined:
    June 10, 2007
    Messages:
    6,447
    Likes Received:
    4,227
    Gender:
    Male
    Occupation:
    Software Test Engineer
    Location:
    Sydney
    Ratings:
    +5,365 / 0
    Home Country:
    Australia Australia
    I wont be able to help on this for a little while as im pretty busy, but can I suggest that you enable SQL logging and see what SQL is generated from the query you build then test it in SQLiteBrowser or similar to see the results. It may be helpful.


     
    • Like Like x 1
  2. Google AdSense Guest Advertisement



    to hide all adverts.
  3. ajs
    • Team MediaPortal

    ajs Development Group

    Joined:
    February 29, 2008
    Messages:
    8,413
    Likes Received:
    2,496
    Gender:
    Male
    Occupation:
    IT
    Location:
    Kyiv
    Ratings:
    +4,416 / 29
    Home Country:
    Ukraine Ukraine
    Show System Specs
    SQL request is not a problem. but can not this be solved by ordinary means.

    --
    WBR, ajs :):whistle::coffee:
     
  4. ltfearme
    • Premium Supporter

    ltfearme Community Plugin Dev

    Joined:
    June 10, 2007
    Messages:
    6,447
    Likes Received:
    4,227
    Gender:
    Male
    Occupation:
    Software Test Engineer
    Location:
    Sydney
    Ratings:
    +5,365 / 0
    Home Country:
    Australia Australia
  5. ajs
    • Team MediaPortal

    ajs Development Group

    Joined:
    February 29, 2008
    Messages:
    8,413
    Likes Received:
    2,496
    Gender:
    Male
    Occupation:
    IT
    Location:
    Kyiv
    Ratings:
    +4,416 / 29
    Home Country:
    Ukraine Ukraine
    Show System Specs
    I try,
    Code (C#):
    1.         else if (CurrentFacade.Type == LatestsFacadeType.Rated)
    2.         {
    3.           SQLCondition conditions = new SQLCondition();
    4.           conditions.Add(new DBOnlineEpisode(), DBOnlineEpisode.cSeriesID, 0, SQLConditionType.GreaterThan);
    5.           conditions.Add(new DBOnlineEpisode(), DBOnlineEpisode.cHidden, 0, SQLConditionType.Equal);
    6.           conditions.Add(new DBOnlineEpisode(), DBOnlineEpisode.cRating, 0, SQLConditionType.GreaterThan);
    7.  
    8.           episodes = DBEpisode.Get(conditions, false);
    9.           if (episodes != null && episodes.Count > 0)
    10.           {
    11.             episodes.OrderByDescending(ep => ep[DBOnlineEpisode.cRating]);
    12.           }
    13.         }
    But result wrong, first elements - Special episodes without rating at all :(
    I try to show conditions to log, and look ... SQL with:
    Code (C#):
    1. conditions.AddOrderItem(DBOnlineEpisode.Q(DBOnlineEpisode.cRating), SQLCondition.orderType.Descending);
    Because no order field (OnlineEpisode Rating) in select ... Only LocalEpisode field ...
     
  6. ajs
    • Team MediaPortal

    ajs Development Group

    Joined:
    February 29, 2008
    Messages:
    8,413
    Likes Received:
    2,496
    Gender:
    Male
    Occupation:
    IT
    Location:
    Kyiv
    Ratings:
    +4,416 / 29
    Home Country:
    Ukraine Ukraine
    Show System Specs
    Bad solution but seems work ...
    Code (C#):
    1.         else if (CurrentFacade.Type == LatestsFacadeType.Rated)
    2.         {
    3.           SQLCondition conditions = new SQLCondition();
    4.           conditions.Add(new DBOnlineEpisode(), DBOnlineEpisode.cSeriesID, 0, SQLConditionType.GreaterThan);
    5.           conditions.Add(new DBOnlineEpisode(), DBOnlineEpisode.cHidden, 0, SQLConditionType.Equal);
    6.           conditions.Add(new DBOnlineEpisode(), DBOnlineEpisode.cRating, 0, SQLConditionType.GreaterThan);
    7.           if (CurrentFacade.UnWatched)
    8.           {
    9.             conditions.Add(new DBOnlineEpisode(), DBOnlineEpisode.cWatched, 1, SQLConditionType.NotEqual);
    10.           }
    11.           // conditions.AddOrderItem(DBOnlineEpisode.Q(DBOnlineEpisode.cRating), SQLCondition.orderType.Descending);
    12.  
    13.           episodes = DBEpisode.Get(conditions, false);
    14.           if (episodes != null && episodes.Count > 0)
    15.           {
    16.             episodes.Sort(delegate (DBEpisode ep1, DBEpisode ep2)
    17.             {
    18.               float r1 = ep1[DBOnlineEpisode.cRating];
    19.               float r2 = ep2[DBOnlineEpisode.cRating];
    20.               return r2.CompareTo(r1);
    21.             });
    22.           }
    23.         }
    upload_2018-1-30_10-37-45.png upload_2018-1-30_10-39-45.png
    Ups ... not work :( return Episodes not present on Media folder (not in local episodes) ... :(
     
  7. ajs
    • Team MediaPortal

    ajs Development Group

    Joined:
    February 29, 2008
    Messages:
    8,413
    Likes Received:
    2,496
    Gender:
    Male
    Occupation:
    IT
    Location:
    Kyiv
    Ratings:
    +4,416 / 29
    Home Country:
    Ukraine Ukraine
    Show System Specs
    SQL:
    Code (SQL):
    1. SELECT online_episodes.CompositeID, online_episodes.EpisodeID, online_episodes.SeriesID, online_episodes.EpisodeIndex, online_episodes.SeasonIndex, online_episodes.EpisodeName, online_episodes.Watched, online_episodes.PlayCount, online_episodes.LastWatchedDate, online_episodes.FirstWatchedDate, online_episodes.Rating AS online_episodesRating , online_episodes.Rating AS online_episodesRating COUNT, online_episodes.myRating, online_episodes.myRatingAt, online_episodes.Summary, online_episodes.FirstAired, online_episodes.OnlineDataImported, online_episodes.GuestStars, online_episodes.Director, online_episodes.Writer, online_episodes.Hidden, online_episodes.lastupdated, online_episodes.ThumbUrl, online_episodes.thumbFilename, online_episodes.Combined_episodenumber, online_episodes.Combined_season, online_episodes.DVD_chapter, online_episodes.DVD_discid, online_episodes.DVD_episodenumber, online_episodes.DVD_season, online_episodes.EpImgFlag, online_episodes.IMDB_ID, online_episodes.Language, online_episodes.ProductionCode, online_episodes.absolute_number, online_episodes.airsafter_season, online_episodes.airsbefore_episode, online_episodes.airsbefore_season, online_episodes.seasonid, online_episodes.thumb_added, online_episodes.thumb_height, online_episodes.thumb_width, online_episodes.is_movie, local_episodes.EpisodeFilename, local_episodes.OriginalComposite, local_episodes.CompositeID, local_episodes.SeriesID, local_episodes.SeasonIndex, local_episodes.EpisodeIndex, local_episodes.LocalEpisodeName, local_episodes.LocalImportProcessed, local_episodes.CompositeUpdated, local_episodes.AvailableSubtitles, local_episodes.OriginalComposite2, local_episodes.CompositeID2, local_episodes.EpisodeIndex2, local_episodes.videoWidth, local_episodes.videoHeight, local_episodes.FileDateAdded, local_episodes.FileDateCreated, local_episodes.IsAvailable, local_episodes.ext, local_episodes.Removable, local_episodes.localPlaytime, local_episodes.VideoCodec, local_episodes.VideoFormat, local_episodes.VideoFormatProfile, local_episodes.VideoBitrate, local_episodes.VideoFrameRate, local_episodes.VideoAspectRatio, local_episodes.AudioCodec, local_episodes.AudioFormat, local_episodes.AudioFormatProfile, local_episodes.AudioBitrate, local_episodes.AudioChannels, local_episodes.AudioTracks, local_episodes.TextCount, local_episodes.StopTime, local_episodes.VolumeLabel, local_episodes.DateWatched, local_episodes.AudioLanguage FROM online_episodes LEFT JOIN local_episodes ON (local_episodes.CompositeID=online_episodes.CompositeID)  WHERE online_episodes.SeriesID > 0 AND online_episodes.Hidden = 0 AND online_episodes.Rating > '0' AND online_episodes.Watched != 1 AND online_episodes.CompositeID NOT IN (SELECT DISTINCT local_episodes.CompositeID2 FROM local_episodes WHERE local_episodes.CompositeID2 != '') UNION SELECT online_episodes.CompositeID, online_episodes.EpisodeID, online_episodes.SeriesID, online_episodes.EpisodeIndex, online_episodes.SeasonIndex, online_episodes.EpisodeName, online_episodes.Watched, online_episodes.PlayCount, online_episodes.LastWatchedDate, online_episodes.FirstWatchedDate, online_episodes.Rating AS online_episodesRating , online_episodes.Rating AS online_episodesRating COUNT, online_episodes.myRating, online_episodes.myRatingAt, online_episodes.Summary, online_episodes.FirstAired, online_episodes.OnlineDataImported, online_episodes.GuestStars, online_episodes.Director, online_episodes.Writer, online_episodes.Hidden, online_episodes.lastupdated, online_episodes.ThumbUrl, online_episodes.thumbFilename, online_episodes.Combined_episodenumber, online_episodes.Combined_season, online_episodes.DVD_chapter, online_episodes.DVD_discid, online_episodes.DVD_episodenumber, online_episodes.DVD_season, online_episodes.EpImgFlag, online_episodes.IMDB_ID, online_episodes.Language, online_episodes.ProductionCode, online_episodes.absolute_number, online_episodes.airsafter_season, online_episodes.airsbefore_episode, online_episodes.airsbefore_season, online_episodes.seasonid, online_episodes.thumb_added, online_episodes.thumb_height, online_episodes.thumb_width, online_episodes.is_movie, local_episodes.EpisodeFilename, local_episodes.OriginalComposite, local_episodes.CompositeID, local_episodes.SeriesID, local_episodes.SeasonIndex, local_episodes.EpisodeIndex, local_episodes.LocalEpisodeName, local_episodes.LocalImportProcessed, local_episodes.CompositeUpdated, local_episodes.AvailableSubtitles, local_episodes.OriginalComposite2, local_episodes.CompositeID2, local_episodes.EpisodeIndex2, local_episodes.videoWidth, local_episodes.videoHeight, local_episodes.FileDateAdded, local_episodes.FileDateCreated, local_episodes.IsAvailable, local_episodes.ext, local_episodes.Removable, local_episodes.localPlaytime, local_episodes.VideoCodec, local_episodes.VideoFormat, local_episodes.VideoFormatProfile, local_episodes.VideoBitrate, local_episodes.VideoFrameRate, local_episodes.VideoAspectRatio, local_episodes.AudioCodec, local_episodes.AudioFormat, local_episodes.AudioFormatProfile, local_episodes.AudioBitrate, local_episodes.AudioChannels, local_episodes.AudioTracks, local_episodes.TextCount, local_episodes.StopTime, local_episodes.VolumeLabel, local_episodes.DateWatched, local_episodes.AudioLanguage FROM online_episodes LEFT JOIN local_episodes ON (local_episodes.CompositeID2=online_episodes.CompositeID)  WHERE online_episodes.SeriesID > 0 AND online_episodes.Hidden = 0 AND online_episodes.Rating > '0' AND online_episodes.Watched != 1 AND local_episodes.CompositeID2 != '' ORDER BY online_episodesRating DESC ', Query = 'SELECT online_episodes.CompositeID, online_episodes.EpisodeID, online_episodes.SeriesID, online_episodes.EpisodeIndex, online_episodes.SeasonIndex, online_episodes.EpisodeName, online_episodes.Watched, online_episodes.PlayCount, online_episodes.LastWatchedDate, online_episodes.FirstWatchedDate, online_episodes.Rating AS online_episodesRating , online_episodes.Rating AS online_episodesRating COUNT, online_episodes.myRating, online_episodes.myRatingAt, online_episodes.Summary, online_episodes.FirstAired, online_episodes.OnlineDataImported, online_episodes.GuestStars, online_episodes.Director, online_episodes.Writer, online_episodes.Hidden, online_episodes.lastupdated, online_episodes.ThumbUrl, online_episodes.thumbFilename, online_episodes.Combined_episodenumber, online_episodes.Combined_season, online_episodes.DVD_chapter, online_episodes.DVD_discid, online_episodes.DVD_episodenumber, online_episodes.DVD_season, online_episodes.EpImgFlag, online_episodes.IMDB_ID, online_episodes.Language, online_episodes.ProductionCode, online_episodes.absolute_number, online_episodes.airsafter_season, online_episodes.airsbefore_episode, online_episodes.airsbefore_season, online_episodes.seasonid, online_episodes.thumb_added, online_episodes.thumb_height, online_episodes.thumb_width, online_episodes.is_movie, local_episodes.EpisodeFilename, local_episodes.OriginalComposite, local_episodes.CompositeID, local_episodes.SeriesID, local_episodes.SeasonIndex, local_episodes.EpisodeIndex, local_episodes.LocalEpisodeName, local_episodes.LocalImportProcessed, local_episodes.CompositeUpdated, local_episodes.AvailableSubtitles, local_episodes.OriginalComposite2, local_episodes.CompositeID2, local_episodes.EpisodeIndex2, local_episodes.videoWidth, local_episodes.videoHeight, local_episodes.FileDateAdded, local_episodes.FileDateCreated, local_episodes.IsAvailable, local_episodes.ext, local_episodes.Removable, local_episodes.localPlaytime, local_episodes.VideoCodec, local_episodes.VideoFormat, local_episodes.VideoFormatProfile, local_episodes.VideoBitrate, local_episodes.VideoFrameRate, local_episodes.VideoAspectRatio, local_episodes.AudioCodec, local_episodes.AudioFormat, local_episodes.AudioFormatProfile, local_episodes.AudioBitrate, local_episodes.AudioChannels, local_episodes.AudioTracks, local_episodes.TextCount, local_episodes.StopTime, local_episodes.VolumeLabel, local_episodes.DateWatched, local_episodes.AudioLanguage FROM online_episodes LEFT JOIN local_episodes ON (local_episodes.CompositeID=online_episodes.CompositeID)  WHERE online_episodes.SeriesID > 0 AND online_episodes.Hidden = 0 AND online_episodes.Rating > '0' AND online_episodes.Watched != 1 AND online_episodes.CompositeID NOT IN (SELECT DISTINCT local_episodes.CompositeID2 FROM local_episodes WHERE local_episodes.CompositeID2 != '') UNION SELECT online_episodes.CompositeID, online_episodes.EpisodeID, online_episodes.SeriesID, online_episodes.EpisodeIndex, online_episodes.SeasonIndex, online_episodes.EpisodeName, online_episodes.Watched, online_episodes.PlayCount, online_episodes.LastWatchedDate, online_episodes.FirstWatchedDate, online_episodes.Rating AS online_episodesRating , online_episodes.Rating AS online_episodesRating COUNT, online_episodes.myRating, online_episodes.myRatingAt, online_episodes.Summary, online_episodes.FirstAired, online_episodes.OnlineDataImported, online_episodes.GuestStars, online_episodes.Director, online_episodes.Writer, online_episodes.Hidden, online_episodes.lastupdated, online_episodes.ThumbUrl, online_episodes.thumbFilename, online_episodes.Combined_episodenumber, online_episodes.Combined_season, online_episodes.DVD_chapter, online_episodes.DVD_discid, online_episodes.DVD_episodenumber, online_episodes.DVD_season, online_episodes.EpImgFlag, online_episodes.IMDB_ID, online_episodes.Language, online_episodes.ProductionCode, online_episodes.absolute_number, online_episodes.airsafter_season, online_episodes.airsbefore_episode, online_episodes.airsbefore_season, online_episodes.seasonid, online_episodes.thumb_added, online_episodes.thumb_height, online_episodes.thumb_width, online_episodes.is_movie, local_episodes.EpisodeFilename, local_episodes.OriginalComposite, local_episodes.CompositeID, local_episodes.SeriesID, local_episodes.SeasonIndex, local_episodes.EpisodeIndex, local_episodes.LocalEpisodeName, local_episodes.LocalImportProcessed, local_episodes.CompositeUpdated, local_episodes.AvailableSubtitles, local_episodes.OriginalComposite2, local_episodes.CompositeID2, local_episodes.EpisodeIndex2, local_episodes.videoWidth, local_episodes.videoHeight, local_episodes.FileDateAdded, local_episodes.FileDateCreated, local_episodes.IsAvailable, local_episodes.ext, local_episodes.Removable, local_episodes.localPlaytime, local_episodes.VideoCodec, local_episodes.VideoFormat, local_episodes.VideoFormatProfile, local_episodes.VideoBitrate, local_episodes.VideoFrameRate, local_episodes.VideoAspectRatio, local_episodes.AudioCodec, local_episodes.AudioFormat, local_episodes.AudioFormatProfile, local_episodes.AudioBitrate, local_episodes.AudioChannels, local_episodes.AudioTracks, local_episodes.TextCount, local_episodes.StopTime, local_episodes.VolumeLabel, local_episodes.DateWatched, local_episodes.AudioLanguage FROM online_episodes LEFT JOIN local_episodes ON (local_episodes.CompositeID2=online_episodes.CompositeID)  WHERE online_episodes.SeriesID > 0 AND online_episodes.Hidden = 0 AND online_episodes.Rating > '0' AND online_episodes.Watched != 1 AND local_episodes.CompositeID2 != '' ORDER BY online_episodesRating DESC
    Error:
    Code (Text):
    1. 2018-01-30 12:16:36.768 [INFO][01]: SQL Execution Failed. Reason = 'SQLiteClient: TVSeriesDatabase4.db3 cmd:sqlite3_prepare16:pvm=null err:ERROR detailed:near "Count"
    Wrong SQL syntax on:
    ... online_episodes.Rating as online_episodesRating Count, ...
     
  8. ajs
    • Team MediaPortal

    ajs Development Group

    Joined:
    February 29, 2008
    Messages:
    8,413
    Likes Received:
    2,496
    Gender:
    Male
    Occupation:
    IT
    Location:
    Kyiv
    Ratings:
    +4,416 / 29
    Home Country:
    Ukraine Ukraine
    Show System Specs
    I try change all online_episodesRating Count to online_episodesRating_Count but SQL Expert show Error:
    upload_2018-1-30_12-44-45.png
     
  9. ajs
    • Team MediaPortal

    ajs Development Group

    Joined:
    February 29, 2008
    Messages:
    8,413
    Likes Received:
    2,496
    Gender:
    Male
    Occupation:
    IT
    Location:
    Kyiv
    Ratings:
    +4,416 / 29
    Home Country:
    Ukraine Ukraine
    Show System Specs
    Log in attach, Code:
    Code (C#):
    1.         else if (CurrentFacade.Type == LatestsFacadeType.Rated)
    2.         {
    3.           SQLCondition conditions = new SQLCondition();
    4.           conditions.Add(new DBOnlineEpisode(), DBOnlineEpisode.cSeriesID, 0, SQLConditionType.GreaterThan);
    5.           conditions.Add(new DBOnlineEpisode(), DBOnlineEpisode.cHidden, 0, SQLConditionType.Equal);
    6.           conditions.Add(new DBOnlineEpisode(), DBOnlineEpisode.cRating, 0, SQLConditionType.GreaterThan);
    7.           if (CurrentFacade.UnWatched)
    8.           {
    9.             conditions.Add(new DBOnlineEpisode(), DBOnlineEpisode.cWatched, 1, SQLConditionType.NotEqual);
    10.           }
    11.           conditions.AddOrderItem(DBOnlineEpisode.Q(DBOnlineEpisode.cRating), SQLCondition.orderType.Descending);
    12.  
    13.           episodes = DBEpisode.Get(conditions, false);
    14.         }
     

    Attached Files:

  10. ajs
    • Team MediaPortal

    ajs Development Group

    Joined:
    February 29, 2008
    Messages:
    8,413
    Likes Received:
    2,496
    Gender:
    Male
    Occupation:
    IT
    Location:
    Kyiv
    Ratings:
    +4,416 / 29
    Home Country:
    Ukraine Ukraine
    Show System Specs
    I found error, DBEpisode.cs line 1615…
    And i create PullRequest (Github online) with changes, replace Replace to Regex.Replace...

    --
    WBR, ajs :):whistle::coffee:
     
    • Thank You! Thank You! x 1
  11. ajs
    • Team MediaPortal

    ajs Development Group

    Joined:
    February 29, 2008
    Messages:
    8,413
    Likes Received:
    2,496
    Gender:
    Male
    Occupation:
    IT
    Location:
    Kyiv
    Ratings:
    +4,416 / 29
    Home Country:
    Ukraine Ukraine
    Show System Specs
Loading...

Users Viewing Thread (Users: 0, Guests: 0)

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice
  • About The Project

    The vision of the MediaPortal project is to create a free open source media centre application, which supports all advanced media centre functions, and is accessible to all Windows users.

    In reaching this goal we are working every day to make sure our software is one of the best.

             

  • Support MediaPortal!

    The team works very hard to make sure the community is running the best HTPC-software. We give away MediaPortal for free but hosting and software is not for us.

    Care to support our work with a few bucks? We'd really appreciate it!