mercredi 5 octobre 2016

Laravel 'join on' where two values are within 'x' of each other

I have a MySQL query where I join on two values when they are +/- '3' of each other:

...
inner join 
on ABS(i.direction - i2.MWD) < 3 
and ABS(i.direction - i2.MWD) < 3

(I'm not entirely sure why the 'and' statement is exactly the same, but that's what made it work)

I've tried converting this to Eloquent using both

$join->on('i.direction', '-', 'i2.MWD', '<' 3)

and

$join->on('i.direction', '=', 'i2.MWD')
  ->where('i.direction', '-', 'i2.MWD', '<' 3)

The above two examples give me an empty result. If I change the '<' to '>' in either example, I get all the joined data from both tables.

I've also tried using DB::raw as a workaround:

$join->on(DB::raw('ABS(input.direction - i2.MWD) < 3 
and ABS(input.direction - i2.MWD) < 3'));

which gives me the below error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'on clause' 
(SQL: select count(*) as aggregate 
from (select * from `input` 
inner join 
 (select MWD, SWH, SWP, BUOY,Time from stations order by Time desc limit 4) 
  as i2 on ABS(input.direction - i2.MWD) < 3 
  and ABS(input.direction - i2.MWD) < 3 = ``) count_row_table)

(the trailing = '' which is being inserted after the 'i2.MWD) < 3' is confusing me).

I'd be very grateful if someone could help me come up with a solution for joining on the two values i.direction & i2.MWD (plus or minus 3) using Eloquent.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire