mysql - Laravel 4.2 Eloquent 关系 leftjoin ON 复杂

Question

Mysql查询

    LEFT JOIN matches as m
    ON sched.league_id = m.leagueid
    AND sched.starttime >= m.start_time
    AND (sched.team_a_id = m.radiant_team_id AND sched.team_b_id = m.dire_team_id)
    OR (sched.team_b_id = m.radiant_team_id AND sched.team_a_id = m.dire_team_id)

Laravel Eloquent

    ->leftJoin('matches as m', function($join){
        $join->on('league_id','=','m.leagueid');
        $join->on('starttime','=','m.start_time');
        // @TODO  AND (sched.team_a_id = m.radiant_team_id AND sched.team_b_id = m.dire_team_id)
        // @TODO OR (sched.team_b_id = m.radiant_team_id AND sched.team_a_id = m.dire_team_id)
    })

问题

• 如何左加入(某事=某事AND某事=某事)OR(某事=某事AND某事=某事)使用Laravel Eloquent

Answer 1

你的 MySQL 查询首先是错误的，我确信这就是你想要的:

LEFT JOIN matches as m
ON sched.league_id = m.leagueid
AND sched.starttime >= m.start_time
AND (
  sched.team_a_id = m.radiant_team_id AND sched.team_b_id = m.dire_team_id
  OR sched.team_b_id = m.radiant_team_id AND sched.team_a_id = m.dire_team_id
)

现在，不幸的是 Laravel 无法构建此连接。

所以唯一的方法是:

->leftJoin('matches as m', function ($j) {
   $j->on('league_id','=','m.leagueid')
     ->on('starttime','=','m.start_time')
     ->on(sched.team_a_id = m.radiant_team_id)
     ->on(sched.team_b_id = m.dire_team_id)
     ->orOn('league_id','=','m.leagueid')
     ->on('starttime','=','m.start_time')
     ->on(sched.team_b_id = m.radiant_team_id)
     ->on(sched.team_a_id = m.dire_team_id);
})

或使用原始语句(注意DB::raw内的括号):

->leftJoin('matches as m', function ($j) {
   $j->on('league_id','=','m.leagueid')
    ->on('starttime','=','m.start_time')
    ->on(
     DB::raw( '(sched.team_a_id',
     '=', 
     DB::raw('m.radiant_team_id AND sched.team_b_id = m.start_time'
  .'OR sched.team_b_id = m.radiant_team_id AND sched.team_a_id = m.start_time)')
    );
})