Kodi Community Forum
Req DB-View tvshowcounts not correct? - Printable Version

+- Kodi Community Forum (https://forum.kodi.tv)
+-- Forum: Development (https://forum.kodi.tv/forumdisplay.php?fid=32)
+--- Forum: Kodi Application (https://forum.kodi.tv/forumdisplay.php?fid=93)
+--- Thread: Req DB-View tvshowcounts not correct? (/showthread.php?tid=336037)



DB-View tvshowcounts not correct? - wwessel - 2018-10-01

I'm using Kodi with a mysql database. In my opinion the view "tvshowcounts" is not correct.

The actual definition looks like this:

CREATE ALGORITHM=UNDEFINED DEFINER=`kodi`@`%` SQL SECURITY DEFINER VIEW `tvshowcounts` AS select `tvshow`.`idShow` AS `idShow`,max(`files`.`lastPlayed`) AS `lastPlayed`,nullif(count(`episode`.`c12`),0) AS `totalCount`,count(`files`.`playCount`) AS `watchedcount`,nullif(count(distinct `episode`.`c12`),0) AS `totalSeasons`,max(`files`.`dateAdded`) AS `dateAdded` from ((`tvshow` left join `episode` on((`episode`.`idShow` = `tvshow`.`idShow`))) left join `files` on((`files`.`idFile` = `episode`.`idFile`))) group by `tvshow`.`idShow`;

This (max) results in not showing the unseen TV-shows in the right order. Every time I add a new season to an unseen TV-show this one is shown at the last place on the home screen, independend of the date when the show was added the first time at all.

So I guess, this definition is the better way:

CREATE ALGORITHM=UNDEFINED DEFINER=`kodi`@`%` SQL SECURITY DEFINER VIEW `tvshowcounts` AS select `tvshow`.`idShow` AS `idShow`,max(`files`.`lastPlayed`) AS `lastPlayed`,nullif(count(`episode`.`c12`),0) AS `totalCount`,count(`files`.`playCount`) AS `watchedcount`,nullif(count(distinct `episode`.`c12`),0) AS `totalSeasons`,min(`files`.`dateAdded`) AS `dateAdded` from ((`tvshow` left join `episode` on((`episode`.`idShow` = `tvshow`.`idShow`))) left join `files` on((`files`.`idFile` = `episode`.`idFile`))) group by `tvshow`.`idShow`;

Any opinions?