WebClient - Continue Watching

[MBSki 1039]

31 minutes ago, Luke said:

Actually it's a newer thing that was added in 4.6, and I think the issue is more likely that older episodes prior to the advent of it never got those values filled in. Something must have went wrong with the db upgrade for those values.

Luke, do you suspect the same or similar cause with this issue: 

[Happy2Play 8399]

16 hours ago, Luke said:

SortParentIndexNumber,IndexNumber

Aren't these just for Specials (airs during season, airs after episode)

[Luke 37336]

2 hours ago, Happy2Play said:

Aren't these just for Specials (airs during season, airs after episode)

Yes, although the queries handle null using coalesce, so for example:

coalesce(SortParentIndexNumber, ParentIndexNumber)

So nulls are OK.

[TeamB 2356]

1 hour ago, Luke said:

Yes, although the queries handle null using coalesce, so for example:

coalesce(SortParentIndexNumber, ParentIndexNumber)

So nulls are OK.

so its a legacy data migration one off issue?

I that case should I just set all my SortParentIndexNumber to null since all new tv shows episodes look like they are null anyway?

[Happy2Play 8399]

6 minutes ago, TeamB said:

I that case should I just set all my SortParentIndexNumber to null since all new tv shows episodes look like they are null anyway?

Have you added any specials (airs during season, airs after episode)?  These are the only episode I see this information one.

[TeamB 2356]

I have 22 episodes where the SortParentIndexNumber is not null or 0

select *
from MediaItems
where SortParentIndexNumber is not NULL
and SortParentIndexNumber != 0
and type = 8

and yes they are all specials

So would a better solution to keep specials in place be

update MediaItems set SortParentIndexNumber = null where SortParentIndexNumber = 0 and type = 8

 

 

[Luke 37336]

I would just turn off the server option to show specials in the season they aired in. Then the query will be different and it wont use sortparentindexnumber.

[TeamB 2356]

16 minutes ago, Luke said:

I would just turn off the server option to show specials in the season they aired in. Then the query will be different and it wont use sortparentindexnumber.

ok, where is this setting?

[Happy2Play 8399]

3 minutes ago, TeamB said:

20 minutes ago, Luke said:

I would just turn off the server option to show specials in the season they aired in. Then the query will be different and it wont use sortparentindexnumber.

ok, where is this setting?

Setting-Library, Advanced tab

[TeamB 2356]

1 hour ago, Luke said:

I would just turn off the server option to show specials in the season they aired in. Then the query will be different and it wont use sortparentindexnumber.

unfortunately that did not fix the problem, it still jumps to the next episode that has a null in the sortparentindexnumber skipping all the unwatched episodes that have a 0 in sortparentindexnumber

[Luke 37336]

Quote

update MediaItems set SortParentIndexNumber = null where SortParentIndexNumber = 0 and type = 8

OK I guess you could try this. I would backup library.db first.

If that doesn't pan out, then if you go to your config/system.xml and set LogAllQueryTimes to true, then enable debug logging, then the query will get logged to the server log. you can take that and run it in a standalone database browser and that might help reveal the problem.

 

[TeamB 2356]

4 minutes ago, Luke said:

If that doesn't pan out, then if you go to your config/system.xml and set LogAllQueryTimes to true, then enable debug logging, then the query will get logged to the server log. you can take that and run it in a standalone database browser and that might help reveal the problem.

I will try this first, the more you know etc etc

[Luke 37336]

The query is going to be large and hard to break down. It's just a reflection of the amount of features we have to support. So just brace yourself.

[TeamB 2356]

I reformatted and unwound the query

 

select 
	A.Id,
	A.IndexNumber,
	A.Name,
	A.Path,
	A.ParentIndexNumber,
	A.RunTimeTicks,
	A.Container,
	A.guid,
	A.ParentId,
	A.IsVirtualItem,
	A.SeriesName,
	A.Album,
	A.AlbumId,
	A.SeriesId,
	A.Images,
	A.SortIndexNumber,
	A.SortParentIndexNumber,
	A.IndexNumberEnd,
	UserDatas.IsFavorite,
	UserDatas.Played,
	UserDatas.PlayCount,
	UserDatas.PlaybackPositionTicks,
	UserDatas.LastPlayedDateInt,
	UserDatas.AudioStreamIndex,
	UserDatas.SubtitleStreamIndex,
	((Coalesce(SortParentIndexNumber,ParentIndexNumber, 1) * 1000000) + Coalesce(SortIndexNumber, IndexNumber, 0) + (Select Case When Coalesce(ParentIndexNumber,1)=0 Then 0 Else 0.5 End) + (Select Case When Coalesce(ParentIndexNumber,1)=0 Then (Cast(Coalesce(IndexNumber, 0) as REAL) / 100000) Else 0 End)) EpisodeAbsoluteIndexNumber 
from MediaItems A 
left join (
		Select 
			N.SeriesPresentationUniqueKey,
			((Coalesce(N.SortParentIndexNumber,N.ParentIndexNumber, 1) * 1000000) + Coalesce(N.SortIndexNumber, N.IndexNumber, 0) + (Select Case When Coalesce(N.ParentIndexNumber,1)=0 Then 0 Else 0.5 End) + (Select Case When Coalesce(N.ParentIndexNumber,1)=0 Then (Cast(Coalesce(N.IndexNumber, 0) as REAL) / 100000) Else 0 End)) AbsoluteIndexNumber,
			UserDatas_N.LastPlayedDateInt LastPlayedDateInt,
			UserDatas_N.playbackPositionTicks playbackPositionTicks 
		from MediaItems N 
		join UserDatas UserDatas_N on N.UserDataKeyId=UserDatas_N.UserDataKeyId And (UserDatas_N.UserId=4) 
		where Type=8 
		and (UserDatas_N.Played=1 or UserDatas_N.playbackPositionTicks > 0)
		Group By N.SeriesPresentationUniqueKey 
		ORDER BY MAX(UserDatas_N.LastPlayedDateInt) desc, Coalesce(SortParentIndexNumber,ParentIndexNumber) desc, Coalesce(SortIndexNumber,IndexNumber) desc
	) LastWatchedEpisodes on LastWatchedEpisodes.SeriesPresentationUniqueKey = A.SeriesPresentationUniqueKey 
left join UserDatas on A.UserDataKeyId=UserDatas.UserDataKeyId And (UserDatas.UserId=4) 
where ((UserDatas.playbackPositionTicks > 0) OR (A.Type=8 and Coalesce(UserDatas.Played,0)=0 and EpisodeAbsoluteIndexNumber > LastWatchedEpisodes.AbsoluteIndexNumber and LastWatchedEpisodes.LastPlayedDateInt not null)) 
AND (A.Type <> 8 or UserDatas.playbackPositionTicks > 0 or Coalesce(A.SortParentIndexNumber, A.ParentIndexNumber, -1) <> 0) 
AND type=8 
AND Id not in (select itemid from ItemLinks where LinkedId in (-1)) 
AND Id not in (select ItemId from AncestorIds2 where AncestorId in (select itemid from ItemLinks where LinkedId in (-1) and type=4)) 
AND IsVirtualItem = 0 
--AND Coalesce(HideFromResume,0) = @HideFromResume 
--AND Coalesce((select HideFromResume from UserDatas where UserDataKeyId=(Select UserDataKeyId from MediaItems MediaItemsHideFromResumeInner where MediaItemsHideFromResumeInner.Id=A.SeriesId) and UserId=4),0)=@HideFromResume 
AND TopParentId in (27162) 
Group by coalesce(A.SeriesPresentationUniqueKey, A.PresentationUniqueKey) 
ORDER BY coalesce(LastWatchedEpisodes.LastPlayedDateInt, Max(UserDatas.LastPlayedDateInt)) DESC,Max(UserDatas.LastPlayedDateInt) DESC,Min(EpisodeAbsoluteIndexNumber) ASC 
LIMIT 12

I hard coded my user and the TopParentId

I can confirm its both the SortParentIndexNumber and SortIndexNumber that are causing my issues.

From what I can see they should not have default values of 0 and 10000 but they do and as you said it is probably due to a data migration script.

Not really sure what the best way forward is, either set the values in the DB to null like I said above or make that above query even more complex with some extra case statements.

I think I will just set all my data to null as that will work for me now.

update MediaItems set SortParentIndexNumber = null, SortIndexNumber = null where SortParentIndexNumber = 0 and type = 8

 

Edited June 14, 2022 by TeamB

[Luke 37336]

Were any of the episodes exhibiting the problem recently added? this is actually the 4.5 to 4.6 upgrade, not the 4.6 to 4.7.

[TeamB 2356]

56 minutes ago, Luke said:

this is actually the 4.5 to 4.6 upgrade,

yeah I think I identified that I thought it was the 4.6 update were things changed.

I mostly notice this on TV Shows I set to unwatched to re watch them, after the first episode it jumps to the first episode with SortParetnIndexNumber = null and SortIndexNumber = 10000 and it is all the post 4.6 epidoes that have null|10000 in the fields.

My guess is the migration code inserted 0 instead of null when migrating the DB schema, or something like that.

 

[Luke 37336]

Ok thanks for confirming.

[MBSki 1039]

@Luke Can you please confirm or not if the issue with the Played/Unplayed filters is related to this issue?