leftJoin with replace in condition

A part of an existing plain sql condition contains follow leftJoin part:

LEFT JOIN database.table1 xyz ON REPLACE(table2.SerialNumber, '123HM', 'HM') = xyz.SerialNumber

Now I try it to implement it as a model scope with following

return $query->leftJoin('database.table1', "REPLACE(table2.serialnumber, '123HM', 'HM')", '=', 'database.table1.SerialNumber');

but it throws an error.

Column not found: 1054 Unknown column 'REPLACE([...]

What's wrong? Whats the right syntax?

prodigy7
prodigy7
  • 1 week ago

what's wrong is you're trying to do an SQL injection into your own query. Try doing it this way:

return $query->leftJoin('table1', function($q) {
    $q->on(DB::raw("REPLACE(table2.serialnumber, '123HM', 'HM') = database.table1.SerialNumber"))
});