Assumptions and what we want to achieve
I'm trying to change Larabel's DB from Oracle to MariaDB, but I get the following error
Problem/error message being encountered
[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) {"userId":"","email":"","request_method":"GET","request_url":"","headers":{"cookie":["XSRF-TOKEN=eyJpdiI6InZUVjlYaVE0NHVUMUNFQ2RQeERqUEE9PSIsInZhbHVlIjoiYTR2c1VOa3VnbnI2NlJSWlE2cUZVMHJHMnhzOVwvb2dueTlZeE01TW9rWDVEaXQwdjJiQ3J1aTVKMFB3NUFPd1QiLCJtYWMiOiJkMDQyNzQ5MjBmMzgxYjMwMWRkNmY2ZGVkMzI2ZjM5NmIxNmM4ZGZiYzBlYzFlMGI0YTc4MDIzYWNmNjVjM2FhIn0%3D; fox_session=eyJpdiI6IjRpU2hIMlRkR1wvVVVXTWxkV2Y3MWVBPT0iLCJ2YWx1ZSI6InUxRE84RFlOalVRQm5OZGJRVnBRNEY3SW1weHVFR0pjdHF6R1wvUmtwVVNSK1BqNjNOaXJzN2FBNHBxb1pkQkJDIiwibWFjIjoiNTQ0ZTY1Mzc2OWU4YzM3OTIxODQ1ZWNiY2U1NmVmNmE5YjM3OTYxZTE5ZGY1YTZmOTY0NzlkNTdlODUxNzJjYSJ9"],"referer":["https://apis.littlefox.com/web/studylog/summary"],"connection":["keep-alive"],"user-agent":["Mozilla/5.0 (iPhone; CPU iPhone OS 15_3 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15E148"],"accept":["*/*"],"api-user-agent":["LF_APP_iOS:phone/2.6.5/iPhone14,5/iOS:15.3"],"api-locale":["ko_KR"],"accept-encoding":["gzip, deflate, br"],"accept-language":["ko-KR,ko;q=0.9"],"x-requested-with":["XMLHttpRequest"],"authorization":["Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzUxMiJ9.eyJpc3MiOiJodHRwczpcL1wvYXBpcy5saXR0bGVmb3guY29tXC9hcGlcL3YxXC9zdHVkeS1sb2dcL3N1bW1hcnlcL2J5LXRlcm0iLCJpYXQiOjE2NDY4NzA3MTIsImV4cCI6MTY0OTQ2MjcxMiwibmJmIjoxNjQ2ODcwNzEyLCJqdGkiOiJoOTJvU1JUekxLY0t3c2UyIiwic3ViIjoiVTIwMTgwMzIyMTEzNTUyMDQ1NiIsInBydiI6IjIzYmQ1Yzg5NDlmNjAwYWRiMzllNzAxYzQwMDg3MmRiN2E1OTc2ZjciLCJhdXRoX2tleSI6IjM0MTYwODIyOTkwMzg2NzUiLCJjdXJyZW50X3VzZXJfaWQiOiJVMjAxODAzMjIxMTM1NTIwNDU2IiwiZXhwaXJlX2RhdGUiOjE2ODU3NTk0NDB9.OwVWmectqoTmjk3jL0ECCNNHDH1U_ulbFdH6e7MTqXrhCYufdsiwryA0XDSvwZ4ubQ97zkYZh3dP9I_3d_i2ig"],"host":[""],"content-length":[""],"content-type":[""]},"inputs":[],"exception":"[object] (Illuminate\\Database\\QueryException(code: 42000): 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) at /data/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664, PDOException(code: 42000): 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 at /data/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:326)
[stacktrace]
Source code in question
<?php
namespace App\Api\V1\Controllers\User;
use App\Api\V1\Controllers\BaseController;
use App\Api\V1\Controllers\Content\StoryController;
use App\Api\V1\CustomException;
use App\Api\V1\Resources\ContentCollection;
use App\Api\V1\Resources\StudyLog\ListByDateCollection;
use App\Api\V1\Resources\StudyLog\RecentlySeriesCollection;
use App\Api\V1\Resources\StudyLog\SummaryByTermResource;
use App\Api\V1\Resources\StudyLog\SummaryTotalResource;
use App\Models\Content;
use App\Models\User\CrosswordScore;
use App\Models\User\EbookStudyLog;
use App\Models\User\Quiz\Result as QuizResult;
use App\Models\User\SeriesStudyStat;
use App\Models\User\StartwordsScore;
use App\Models\User\StatActStudyDailyByLevel;
use App\Models\User\StatStudyDaily;
use App\Models\User\StatStudyDailyByLevel;
use App\Models\User\StatStudyHourly;
use App\Models\User\StudyLog;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Validation\Rule;
/**
*
*
* @return SummaryTotalResource
*/
public function summaryTotal()
{
$current_user_id = auth()->payload()->get('current_user_id');
$summary = [
'start_date' => \LittlefoxLocalization::setTimezoneDate(auth()->user()->reg_date)->toDateString(),
'end_date' => \LittlefoxLocalization::setTimezoneDate(now())->toDateString(),
'count' => 0,
'point' => 0,
'time' => 0,
];
if ($animation_latest_study_log = StudyLog::where('fu_id', $current_user_id)->limit(1)->first()) {
$summary['latest_study_time'] = \LittlefoxLocalization::setTimezoneDate($animation_latest_study_log->log_date)->toDateTimeString();
$summary['latest_study_device'] = in_array($animation_latest_study_log->view_type, ['F', 'N'], true) ? 'pc' : 'mobile';
}
if ($ebook_latest_study_log = EbookStudyLog::where('fu_id', $current_user_id)->orderBy('log_date', 'desc')->first()) {
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)) {
$summary['latest_study_time'] = \LittlefoxLocalization::setTimezoneDate($ebook_latest_study_log->log_date)->toDateTimeString();
$summary['latest_study_device'] = $ebook_latest_study_log->device_type == 'PC' ? 'pc' : 'mobile';
}
}
$daily_max_date = now(auth()->user()->timezone)->subDays(2)->endOfDay();
// NOTE:
if ($stat_study_daily_summary = StatStudyDaily::where('fu_id', $current_user_id)->summary()->addSelect(\DB::raw('MAX(st_date) max_date'))->first()) {
if ($stat_study_daily_summary->max_date) {
$daily_max_date = Carbon::parse($stat_study_daily_summary->max_date, auth()->user()->timezone)->endOfDay();
}
$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']));
$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']));
$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']));
}
// NOTE:
$hourly_start_date = $daily_max_date->copy()->addSecond()->startOfSecond()->setTimezone('Asia/Seoul');
$max_date = StatStudyHourly::where('fu_id', $current_user_id)
->where('st_date', '>=', $hourly_start_date->toDateTimeString())
->select(\DB::raw('MAX(st_date) max_date'))
->first();
if ($max_date && $max_date->max_date) {
$hourly_max_date = Carbon::parse($max_date->max_date)->endOfHour();
// NOTE:
$stat_study_hourly_summary = StatStudyHourly::where('fu_id', $current_user_id)
->where('st_date', '>=', $hourly_start_date->toDateTimeString())
->where('st_date', '<=', $hourly_max_date->toDateTimeString())
->summary()
->get();
foreach ($stat_study_hourly_summary as $hourly_summary) {
$summary['count'] += (int)array_sum($hourly_summary->only(['story_one_count', 'story_auto_count', 'story_ebook_count', 'song_one_count', 'song_auto_count']));
$summary['point'] += (int)array_sum($hourly_summary->only(['story_one_point', 'story_auto_point', 'story_ebook_point', 'song_one_point', 'song_auto_point']));
$summary['time'] += (int)array_sum($hourly_summary->only(['story_one_time', 'story_auto_time', 'story_ebook_time', 'song_one_time', 'song_auto_time']));
}
} else {
$hourly_max_date = $daily_max_date;
}
// NOTE:
if ($study_log_summary = StudyLog::where('fu_id', $current_user_id)->where('log_date', '>', $hourly_max_date->toDateTimeString())->summary()->first()) {
$summary['count'] += (int)array_sum($study_log_summary->only(['story_one_count', 'story_auto_count', 'story_ebook_count', 'song_one_count', 'song_auto_count']));
$summary['point'] += (int)array_sum($study_log_summary->only(['story_one_point', 'story_auto_point', 'story_ebook_point', 'song_one_point', 'song_auto_point']));
$summary['time'] += (int)array_sum($study_log_summary->only(['story_one_time', 'story_auto_time', 'story_ebook_time', 'song_one_time', 'song_auto_time']));
}
// NOTE:
if ($ebook_study_log_summary = EbookStudyLog::where('fu_id', $current_user_id)->where('log_date', '>', $hourly_max_date->toDateTimeString())->summary()->first()) {
$summary['count'] += (int)($ebook_study_log_summary->story_ebook_cnt);
$summary['point'] += (int)($ebook_study_log_summary->story_ebook_point);
$summary['time'] += (int)($ebook_study_log_summary->story_ebook_time);
}
return new SummaryTotalResource($summary);
}
What we tried
I debugged it in dd and identified the relevant part and found the cause of the error, but how to convert the Oracle way of writing SUM(DECODE) to MariaDB's CASE expression? I don't know where to go from there.
Supplemental information (e.g., FW/tool version)
Laravel5.7, MariaDB10.3
via
Chebli Mohamed
