fix database view

2 files changed, 97 insertions, 1 deletions

diff --git a/database/migrations/2020_09_18_200839_fix_view_to_include_unaired.php b/database/migrations/2020_09_18_200839_fix_view_to_include_unaired.php
new file mode 100644
index 0000000..5f7e1a2
--- /dev/null
+++ b/database/migrations/2020_09_18_200839_fix_view_to_include_unaired.php
@@ -0,0 +1,96 @@
+<?php
+
+use Illuminate\Database\Migrations\Migration;
+use Illuminate\Database\Schema\Blueprint;
+use Illuminate\Support\Facades\Schema;
+
+class FixViewToIncludeUnaired extends Migration
+{
+    /**
+     * Run the migrations.
+     *
+     * @return void
+     */
+    public function up()
+    {
+        Schema::table('anime', function (Blueprint $table) {
+	    DB::statement(
+		    "CREATE OR REPLACE VIEW view_anime_index AS
+		    SELECT DISTINCT
+			a.mal_id,
+			url,
+			image_url,
+			(CASE WHEN title_eng IS NOT NULL THEN title_eng
+			ELSE title_pref END) as title,
+			title_pref,
+			title_eng,
+			anime_type,
+			a.created_at,
+			a.updated_at,
+			episodes,
+			synopsis,
+			hashtag,
+			airing_status,
+			a.season_name,
+			a.season_year,
+			(SELECT score FROM stats as b WHERE b.mal_id = a.mal_id AND score IS NOT NULL ORDER BY created_at ASC LIMIT 1) as score_begin,
+			(SELECT score FROM stats as b WHERE b.mal_id = a.mal_id ORDER BY created_at DESC LIMIT 1) as score_today,
+			(SELECT watching FROM stats as b WHERE b.mal_id = a.mal_id ORDER BY created_at DESC LIMIT 1) as watching,
+			(SELECT members FROM stats as b WHERE b.mal_id = a.mal_id ORDER BY created_at DESC LIMIT 1) as members
+			FROM
+				anime as a
+			JOIN
+				stats as s ON a.mal_id = s.mal_id
+			ORDER BY
+				abs(score_today-score_begin) DESC,
+				airing_status ASC
+		    ;"
+	    );
+        });
+    }
+
+    /**
+     * Reverse the migrations.
+     *
+     * @return void
+     */
+    public function down()
+    {
+        Schema::table('anime', function (Blueprint $table) {
+	    DB::statement(
+		    "CREATE OR REPLACE VIEW view_anime_index AS
+		    SELECT DISTINCT
+			a.mal_id,
+			url,
+			image_url,
+			(CASE WHEN title_eng IS NOT NULL THEN title_eng
+			ELSE title_pref END) as title,
+			title_pref,
+			title_eng,
+			anime_type,
+			a.created_at,
+			a.updated_at,
+			episodes,
+			synopsis,
+			hashtag,
+			airing_status,
+			a.season_name,
+			a.season_year,
+			(SELECT score FROM stats as b WHERE b.mal_id = a.mal_id AND score IS NOT NULL ORDER BY created_at ASC LIMIT 1) as score_begin,
+			(SELECT score FROM stats as b WHERE b.mal_id = a.mal_id ORDER BY created_at DESC LIMIT 1) as score_today,
+			(SELECT watching FROM stats as b WHERE b.mal_id = a.mal_id ORDER BY created_at DESC LIMIT 1) as watching,
+			(SELECT members FROM stats as b WHERE b.mal_id = a.mal_id ORDER BY created_at DESC LIMIT 1) as members
+			FROM
+				anime as a
+			JOIN
+				stats as s ON a.mal_id = s.mal_id
+			WHERE
+				score IS NOT null
+			ORDER BY
+				abs(score_today-score_begin) DESC,
+				airing_status ASC
+		    ;"
+	    );
+        });
+    }
+}
diff --git a/resources/views/list_anime.blade.php b/resources/views/list_anime.blade.php
index 9596d28..f7e07ab 100644
--- a/resources/views/list_anime.blade.php
+++ b/resources/views/list_anime.blade.php
@@ -38,7 +38,7 @@
 			
 				<h5 class="card-title">{{ $anime->title }} @if ( ! compare($anime->title, $anime->title_pref) )<br><span class="text-muted hide-sm">{{ $anime->title_pref }}</span>@endif</h5>
 				<h6 class="card-subtitle mb-2 text-muted">
-					score: {{ $anime->score_today }}, members: {{ $anime->members }}
+					score: {{ $anime->score_today ? $anime->score_today : "n/a" }}, members: {{ $anime->members }}
 					@if( "surprising_anime" == \Request::route()->getName())
 						<br>
 						@if ( 0 < $anime->score_today - $anime->score_begin )