CODEWITHSUNDEEP
mysqllaravel-4relational-databaseleft-joineloquent
ronscript
ronscript

Reputation: 407

Laravel 4.2 Eloquent relationship leftjoin ON complicated

Mysql Query

    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)
    })

Question

Upvotes: 0

Views: 421

Answers (2)

ronscript
ronscript

Reputation: 407

             $join->on('league_id','=','m.leagueid')
             ->on('starttime','<=','m.start_time')
             ->on(DB::raw('m.radiant_team_id'), 'IN', DB::raw('(team_a_id,team_b_id)'))
             ->on(DB::raw('m.dire_team_id'), 'IN', DB::raw('(team_a_id,team_b_id)'));

Upvotes: 0

Jarek Tkaczyk
Jarek Tkaczyk

Reputation: 81187

Your MySQL query is wrong in the first place, I'm sure this is what you wanted:

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
)

Now, unfortunately Laravel is not capable of building this join.

So the only way is this:

->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);
})

or playing with raw statements (pay attention to parentheses within 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)')
    );
})

Upvotes: 1

Related Questions