[wwessel]

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?