LMH need help! Help plz ... How select episodes in Online Rating sort order (1 Viewer)

ltfearme

Community Plugin Dev
  • Premium Supporter
  • June 10, 2007
    6,751
    7,196
    Sydney
    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.
     

    ajs

    Development Group
  • Team MediaPortal
  • February 29, 2008
    15,492
    10,366
    Kyiv
    Home Country
    Ukraine Ukraine
    • Thread starter
    • Moderator
    • #12
    SQL request is not a problem. but can not this be solved by ordinary means.

    --
    WBR, ajs :):whistle::coffee:
     

    ajs

    Development Group
  • Team MediaPortal
  • February 29, 2008
    15,492
    10,366
    Kyiv
    Home Country
    Ukraine Ukraine
    • Thread starter
    • Moderator
    • #14
    If it's ordinary list of episodes you could try LinQ methods:
    I try,
    C#:
            else if (CurrentFacade.Type == LatestsFacadeType.Rated)
            {
              SQLCondition conditions = new SQLCondition();
              conditions.Add(new DBOnlineEpisode(), DBOnlineEpisode.cSeriesID, 0, SQLConditionType.GreaterThan);
              conditions.Add(new DBOnlineEpisode(), DBOnlineEpisode.cHidden, 0, SQLConditionType.Equal);
              conditions.Add(new DBOnlineEpisode(), DBOnlineEpisode.cRating, 0, SQLConditionType.GreaterThan);
    
              episodes = DBEpisode.Get(conditions, false);
              if (episodes != null && episodes.Count > 0)
              {
                episodes.OrderByDescending(ep => ep[DBOnlineEpisode.cRating]);
              }
            }
    But result wrong, first elements - Special episodes without rating at all :(
    but can I suggest that you enable SQL logging and see what SQL is generated
    I try to show conditions to log, and look ... SQL with:
    C#:
    conditions.AddOrderItem(DBOnlineEpisode.Q(DBOnlineEpisode.cRating), SQLCondition.orderType.Descending);
    Because no order field (OnlineEpisode Rating) in select ... Only LocalEpisode field ...
     

    ajs

    Development Group
  • Team MediaPortal
  • February 29, 2008
    15,492
    10,366
    Kyiv
    Home Country
    Ukraine Ukraine
    • Thread starter
    • Moderator
    • #15
    Bad solution but seems work ...
    C#:
            else if (CurrentFacade.Type == LatestsFacadeType.Rated)
            {
              SQLCondition conditions = new SQLCondition();
              conditions.Add(new DBOnlineEpisode(), DBOnlineEpisode.cSeriesID, 0, SQLConditionType.GreaterThan);
              conditions.Add(new DBOnlineEpisode(), DBOnlineEpisode.cHidden, 0, SQLConditionType.Equal);
              conditions.Add(new DBOnlineEpisode(), DBOnlineEpisode.cRating, 0, SQLConditionType.GreaterThan);
              if (CurrentFacade.UnWatched)
              {
                conditions.Add(new DBOnlineEpisode(), DBOnlineEpisode.cWatched, 1, SQLConditionType.NotEqual);
              }
              // conditions.AddOrderItem(DBOnlineEpisode.Q(DBOnlineEpisode.cRating), SQLCondition.orderType.Descending);
    
              episodes = DBEpisode.Get(conditions, false);
              if (episodes != null && episodes.Count > 0)
              {
                episodes.Sort(delegate (DBEpisode ep1, DBEpisode ep2)
                {
                  float r1 = ep1[DBOnlineEpisode.cRating];
                  float r2 = ep2[DBOnlineEpisode.cRating];
                  return r2.CompareTo(r1);
                });
              }
            }
    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) ... :(
     

    ajs

    Development Group
  • Team MediaPortal
  • February 29, 2008
    15,492
    10,366
    Kyiv
    Home Country
    Ukraine Ukraine
    • Thread starter
    • Moderator
    • #16
    I suggest that you enable SQL logging and see what SQL is generated
    SQL:
    SQL:
    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:
    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, ...
     

    ajs

    Development Group
  • Team MediaPortal
  • February 29, 2008
    15,492
    10,366
    Kyiv
    Home Country
    Ukraine Ukraine
    • Thread starter
    • Moderator
    • #17
    I try change all online_episodesRating Count to online_episodesRating_Count but SQL Expert show Error:
    upload_2018-1-30_12-44-45.png
     

    ajs

    Development Group
  • Team MediaPortal
  • February 29, 2008
    15,492
    10,366
    Kyiv
    Home Country
    Ukraine Ukraine
    • Thread starter
    • Moderator
    • #18
    Log in attach, Code:
    C#:
            else if (CurrentFacade.Type == LatestsFacadeType.Rated)
            {
              SQLCondition conditions = new SQLCondition();
              conditions.Add(new DBOnlineEpisode(), DBOnlineEpisode.cSeriesID, 0, SQLConditionType.GreaterThan);
              conditions.Add(new DBOnlineEpisode(), DBOnlineEpisode.cHidden, 0, SQLConditionType.Equal);
              conditions.Add(new DBOnlineEpisode(), DBOnlineEpisode.cRating, 0, SQLConditionType.GreaterThan);
              if (CurrentFacade.UnWatched)
              {
                conditions.Add(new DBOnlineEpisode(), DBOnlineEpisode.cWatched, 1, SQLConditionType.NotEqual);
              }
              conditions.AddOrderItem(DBOnlineEpisode.Q(DBOnlineEpisode.cRating), SQLCondition.orderType.Descending);
    
              episodes = DBEpisode.Get(conditions, false);
            }
     

    Attachments

    • MP-TVSeries.log
      15.6 MB

    ajs

    Development Group
  • Team MediaPortal
  • February 29, 2008
    15,492
    10,366
    Kyiv
    Home Country
    Ukraine Ukraine
    • Thread starter
    • Moderator
    • #19
    I found error, DBEpisode.cs line 1615…
    And i create PullRequest (Github online) with changes, replace Replace to Regex.Replace...

    --
    WBR, ajs :):whistle::coffee:
     

    Users who are viewing this thread

    Top Bottom