diff options
| -rw-r--r-- | database/migrations/2020_09_18_182028_update_view_to_include_correct_season.php | 96 |
1 files changed, 96 insertions, 0 deletions
diff --git a/database/migrations/2020_09_18_182028_update_view_to_include_correct_season.php b/database/migrations/2020_09_18_182028_update_view_to_include_correct_season.php new file mode 100644 index 0000000..d143182 --- /dev/null +++ b/database/migrations/2020_09_18_182028_update_view_to_include_correct_season.php @@ -0,0 +1,96 @@ +<?php + +use Illuminate\Database\Migrations\Migration; +use Illuminate\Database\Schema\Blueprint; +use Illuminate\Support\Facades\Schema; + +class UpdateViewToIncludeCorrectSeason 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 + WHERE + score IS NOT null + ORDER BY + abs(score_today-score_begin) DESC, + airing_status ASC + ;" + ); + }); + } + + /** + * Reverse the migrations. + * + * @return void + */ + public function down() + { + 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, + (SELECT season_name FROM stats as b WHERE b.mal_id = a.mal_id ORDER BY created_at ASC LIMIT 1) as season_name, + (SELECT season_year FROM stats as b WHERE b.mal_id = a.mal_id ORDER BY created_at ASC LIMIT 1) as 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 + ;" + ); + } +} |