前提・実現したいこと
LaravelのDBをOracleからMariaDBに変更しようとしていますが、以下のエラーが出ます。
発生している問題・エラーメッセージ
[2022-03-10 09:05:14] staging.ERROR: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1,'M',1,0)) story_one_count, SUM(DECODE(stat_study_log.log_type, 'P', CEIL(fo...' at line 1 (SQL: select SUM(DECODE(stat_study_log.log_type, 'P',1,'M',1,0)) story_one_count, SUM(DECODE(stat_study_log.log_type, 'P', CEIL(fox_contents.play_time / 80), 'M', CEIL(fox_contents.play_time / 80), 0)) story_one_point, SUM(DECODE(stat_study_log.log_type, 'P', fox_contents.play_time, 'M', fox_contents.play_time, 0)) story_one_time, SUM(DECODE(stat_study_log.log_type, 'A', 1, 0)) story_auto_count, SUM(DECODE(stat_study_log.log_type, 'A', CEIL(fox_contents.play_time / 80), 0)) story_auto_point, SUM(DECODE(stat_study_log.log_type, 'A', fox_contents.play_time, 0)) story_auto_time, SUM(DECODE(stat_study_log.log_type, 'S', 1, 0)) song_one_count, SUM(DECODE(stat_study_log.log_type, 'S', CEIL(fox_contents.play_time / 80), 0)) song_one_point, SUM(DECODE(stat_study_log.log_type, 'S', fox_contents.play_time, 0)) song_one_time, SUM(DECODE(stat_study_log.log_type, 'B', 1, 0)) song_auto_count, SUM(DECODE(stat_study_log.log_type, 'B', CEIL(fox_contents.play_time / 80), 0)) song_auto_point, SUM(DECODE(stat_study_log.log_type, 'B', fox_contents.play_time, 0)) song_auto_time from `stat_study_log` inner join `fox_contents` on `fox_contents`.`fc_id` = `stat_study_log`.`fc_id` where `fu_id` = U201803221135520456 and `log_date` > 2021-10-12 23:59:59 limit 1)
該当のソースコード
StudyLogController
1<?php 2 3namespace App\Api\V1\Controllers\User; 4 5use App\Api\V1\Controllers\BaseController; 6use App\Api\V1\Controllers\Content\StoryController; 7use App\Api\V1\CustomException; 8use App\Api\V1\Resources\ContentCollection; 9use App\Api\V1\Resources\StudyLog\ListByDateCollection; 10use App\Api\V1\Resources\StudyLog\RecentlySeriesCollection; 11use App\Api\V1\Resources\StudyLog\SummaryByTermResource; 12use App\Api\V1\Resources\StudyLog\SummaryTotalResource; 13use App\Models\Content; 14use App\Models\User\CrosswordScore; 15use App\Models\User\EbookStudyLog; 16use App\Models\User\Quiz\Result as QuizResult; 17use App\Models\User\SeriesStudyStat; 18use App\Models\User\StartwordsScore; 19use App\Models\User\StatActStudyDailyByLevel; 20use App\Models\User\StatStudyDaily; 21use App\Models\User\StatStudyDailyByLevel; 22use App\Models\User\StatStudyHourly; 23use App\Models\User\StudyLog; 24use Carbon\Carbon; 25use Illuminate\Http\Request; 26use Illuminate\Validation\Rule; 27 28 /** 29 * 30 * 31 * @return SummaryTotalResource 32 */ 33 public function summaryTotal() 34 { 35 $current_user_id = auth()->payload()->get('current_user_id'); 36 37 $summary = [ 38 'start_date' => \LittlefoxLocalization::setTimezoneDate(auth()->user()->reg_date)->toDateString(), 39 'end_date' => \LittlefoxLocalization::setTimezoneDate(now())->toDateString(), 40 'count' => 0, 41 'point' => 0, 42 'time' => 0, 43 ]; 44 45 if ($animation_latest_study_log = StudyLog::where('fu_id', $current_user_id)->limit(1)->first()) { 46 $summary['latest_study_time'] = \LittlefoxLocalization::setTimezoneDate($animation_latest_study_log->log_date)->toDateTimeString(); 47 $summary['latest_study_device'] = in_array($animation_latest_study_log->view_type, ['F', 'N'], true) ? 'pc' : 'mobile'; 48 } 49 50 if ($ebook_latest_study_log = EbookStudyLog::where('fu_id', $current_user_id)->orderBy('log_date', 'desc')->first()) { 51 if (is_null($ebook_latest_study_log) || is_null($animation_latest_study_log) || $ebook_latest_study_log->log_date->gt($animation_latest_study_log->log_date)) { 52 $summary['latest_study_time'] = \LittlefoxLocalization::setTimezoneDate($ebook_latest_study_log->log_date)->toDateTimeString(); 53 $summary['latest_study_device'] = $ebook_latest_study_log->device_type == 'PC' ? 'pc' : 'mobile'; 54 } 55 } 56 57 $daily_max_date = now(auth()->user()->timezone)->subDays(2)->endOfDay(); 58 59 // NOTE: 60 if ($stat_study_daily_summary = StatStudyDaily::where('fu_id', $current_user_id)->summary()->addSelect(\DB::raw('MAX(st_date) max_date'))->first()) { 61 if ($stat_study_daily_summary->max_date) { 62 $daily_max_date = Carbon::parse($stat_study_daily_summary->max_date, auth()->user()->timezone)->endOfDay(); 63 } 64 65 $summary['count'] += (int)array_sum($stat_study_daily_summary->only(['story_one_count', 'story_auto_count', 'story_ebook_count', 'song_one_count', 'song_auto_count'])); 66 $summary['point'] += (int)array_sum($stat_study_daily_summary->only(['story_one_point', 'story_auto_point', 'story_ebook_point', 'song_one_point', 'song_auto_point'])); 67 $summary['time'] += (int)array_sum($stat_study_daily_summary->only(['story_one_time', 'story_auto_time', 'story_ebook_time', 'song_one_time', 'song_auto_time'])); 68 } 69 70 // NOTE: 71 $hourly_start_date = $daily_max_date->copy()->addSecond()->startOfSecond()->setTimezone('Asia/Seoul'); 72 73 $max_date = StatStudyHourly::where('fu_id', $current_user_id) 74 ->where('st_date', '>=', $hourly_start_date->toDateTimeString()) 75 ->select(\DB::raw('MAX(st_date) max_date')) 76 ->first(); 77 78 if ($max_date && $max_date->max_date) { 79 $hourly_max_date = Carbon::parse($max_date->max_date)->endOfHour(); 80 81 // NOTE: 82 $stat_study_hourly_summary = StatStudyHourly::where('fu_id', $current_user_id) 83 ->where('st_date', '>=', $hourly_start_date->toDateTimeString()) 84 ->where('st_date', '<=', $hourly_max_date->toDateTimeString()) 85 ->summary() 86 ->get(); 87 88 foreach ($stat_study_hourly_summary as $hourly_summary) { 89 $summary['count'] += (int)array_sum($hourly_summary->only(['story_one_count', 'story_auto_count', 'story_ebook_count', 'song_one_count', 'song_auto_count'])); 90 $summary['point'] += (int)array_sum($hourly_summary->only(['story_one_point', 'story_auto_point', 'story_ebook_point', 'song_one_point', 'song_auto_point'])); 91 $summary['time'] += (int)array_sum($hourly_summary->only(['story_one_time', 'story_auto_time', 'story_ebook_time', 'song_one_time', 'song_auto_time'])); 92 } 93 } else { 94 $hourly_max_date = $daily_max_date; 95 } 96 97 // NOTE: 98 if ($study_log_summary = StudyLog::where('fu_id', $current_user_id)->where('log_date', '>', $hourly_max_date->toDateTimeString())->summary()->first()) { 99 $summary['count'] += (int)array_sum($study_log_summary->only(['story_one_count', 'story_auto_count', 'story_ebook_count', 'song_one_count', 'song_auto_count'])); 100 $summary['point'] += (int)array_sum($study_log_summary->only(['story_one_point', 'story_auto_point', 'story_ebook_point', 'song_one_point', 'song_auto_point'])); 101 $summary['time'] += (int)array_sum($study_log_summary->only(['story_one_time', 'story_auto_time', 'story_ebook_time', 'song_one_time', 'song_auto_time'])); 102 } 103 104 // NOTE: 105 if ($ebook_study_log_summary = EbookStudyLog::where('fu_id', $current_user_id)->where('log_date', '>', $hourly_max_date->toDateTimeString())->summary()->first()) { 106 $summary['count'] += (int)($ebook_study_log_summary->story_ebook_cnt); 107 $summary['point'] += (int)($ebook_study_log_summary->story_ebook_point); 108 $summary['time'] += (int)($ebook_study_log_summary->story_ebook_time); 109 } 110 111 return new SummaryTotalResource($summary); 112 } 113
試したこと
ddでデバッグして該当箇所を特定して、エラーの原因を突き止めたが、Oracleの書き方であるSUM(DECODE)をMariaDBのCASE式にどう換えるのか、
その先がわからない。
補足情報(FW/ツールのバージョンなど)
Laravel5.7, MariaDB10.3
回答1件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
退会済みユーザー
2022/03/10 05:52
2022/03/10 20:21
2022/03/11 01:02
退会済みユーザー
2022/03/11 01:25