在 Laravel 6 中,在高级 Join 语句中,使用 Where 语句的参数分组,以生成多重嵌套条件的 SQL
1、之前有一个实现,是判断具体的某条记录,是否符合某个复杂的嵌套条件,代码实现如下
1 2 3 4 5 6 7 | 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、现在需要查询出所有符合这个复杂的嵌套条件的记录,参考:https://learnku.com/docs/laravel/6.x/queries/5171#2f5914 ,代码实现如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | $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、最终生成的 SQL 符合预期。如图1
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 | 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 |
近期评论