diff options
| -rw-r--r-- | database/migrations/2022_11_12_130638_fix_performance_view_anime_index.php | 97 |
1 files changed, 97 insertions, 0 deletions
diff --git a/database/migrations/2022_11_12_130638_fix_performance_view_anime_index.php b/database/migrations/2022_11_12_130638_fix_performance_view_anime_index.php new file mode 100644 index 0000000..ed064ee --- /dev/null +++ b/database/migrations/2022_11_12_130638_fix_performance_view_anime_index.php @@ -0,0 +1,97 @@ +<?php + +use Illuminate\Database\Migrations\Migration; +use Illuminate\Database\Schema\Blueprint; +use Illuminate\Support\Facades\Schema; + +class FixPerformanceViewAnimeIndex extends Migration +{ + /** + * Run the migrations. + * + * @return void + */ + public function up() + { + Schema::table('view_anime_index', 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, + score_begin, + score_today, + anime_details.watching as watching, + anime_details.members as members + FROM + anime as a + JOIN + stats as s ON a.mal_id = s.mal_id + JOIN + anime_details on anime_details.mal_id = a.mal_id + ORDER BY + abs(score_today-score_begin) DESC, + airing_status ASC + ;" + ); + }); + } + + /** + * Reverse the migrations. + * + * @return void + */ + public function down() + { + Schema::table('view_anime_index', 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 + ;" + ); + }); + } + +} |