In Laravel 6, in advanced JOIN statements, use the parameters of the WHERE statement grouping to generate SQL for multiple nested conditions
1. There was an implementation before, which is to judge a specific record, whether it meets a certain complex nesting condition, and the code is implemented as follows
if ($theme['theme_installation']['type'] == ThemeInstallation::TYPE_UPDATE && ($theme['theme_installation']['processing'] || (!$theme['theme_installation']['processing'] && $theme['theme_installation']['processing_failed']))) {
$themeSaasTasks = ThemeSaasTask::whereJsonContains('theme_task_ids', $theme['theme_installation']['theme_installation_version_preset']['theme_installation_tasks'][0]['id'])->get()->toArray();
if (!empty($themeSaasTasks)) {
return true;
}
}
return false;
2. Now you need to query all records that meet this complex nesting condition, refer to:https://learnku.com/docs/laravel/6.x/queries/5171#2f5914, the code is implemented as follows
$themeInstallationIds = ThemeInstallation::select('theme_installation.id')
->join('theme_installation_task', function ($join) use ($themeTaskIds) {
$join->on('theme_installation.id', '=', 'theme_installation_task.theme_installation_id')
->where('theme_installation.type', ThemeInstallation::TYPE_UPDATE)
->where(function ($query) {
$query->where('theme_installation.processing', true)
->orWhere(function ($query) {
$query->where('theme_installation.processing', false)
->where('theme_installation.processing_failed', true);
});
})
->whereIn('theme_installation_task.id', $themeTaskIds);
})
->get();
3. The final generated SQL is in line with expectations. as shown in Figure 1
Figure 1
select
`theme_installation`.`id`
from
`theme_installation`
inner join `theme_installation_task` on `theme_installation`.`id` = `theme_installation_task`.`theme_installation_id`
and `theme_installation`.`type` = 3
and (
`theme_installation`.`processing` = 1
or (
`theme_installation`.`processing` = 0
and `theme_installation`.`processing_failed` = 1
)
)
and `theme_installation_task`.`id` in (
513,
514,
515,
516,
517,
518,
519,
520,
521,
522,
523,
524,
525,
526,
527,
528,
529,
530,
531,
532,
533,
534,
535,
536,
537,
538,
539,
540,
541,
542,
543,
544,
545,
546,
547,
548,
549,
550,
551,
552,
553,
554,
557,
558,
559,
560,
561,
562,
563,
564,
565,
566,
567,
568,
569,
570,
571,
572,
573,
574,
575,
576,
577,
578,
579,
580,
581,
582,
583,
584,
585,
586,
587,
588,
589,
590,
591,
592,
593,
594,
595,
598,
599,
600,
613,
614,
615,
616,
617,
618
)
where
`theme_installation`.`deleted_at` is null
