Laravel postgresql join error. "Operator does not exist"

Question

我的laravel查询代码

$query = DB::table('sample_point_mission')
->rightJoin('sample_point_record', 'sample_point_mission._id', '=','sample_point_record._pid')
->leftJoin('animal_name', 'sample_point_record.animal_name_id', '=','animal_name.tid::VARCHAR)')
->leftJoin('geographic_unit', 'sample_point_record.geography_unit_id', '=','geographic_unit.tid::VARCHAR');
$query->get();

错误消息===>

SQLSTATE[42883]: Undefined function: 7 ERROR:  operator does not exist: character varying = integer

所以我试着添加它需要的字符

$query = DB::table('sample_point_mission')
->rightJoin('sample_point_record', 'sample_point_mission._id', '=','sample_point_record._pid')
->leftJoin('animal_name', 'sample_point_record.animal_name_id', '=','animal_name.tid::VARCHAR')
->leftJoin('geographic_unit', 'sample_point_record.geography_unit_id', '=','geographic_unit.tid::VARCHAR');
$query->get();

错误消息：

SQLSTATE[42703]: Undefined column: 7 ERROR:  column animal_name.tid::VARCHAR does not exist

Answer 1

我不认为您可以在常规Laravel联接函数中使用原始Postgres SQL。但是，您可以(而且可能必须(将原始SQL与DB::raw一起使用。试试这个版本：

$query = DB::table('sample_point_mission')
->rightJoin('sample_point_record', 'sample_point_mission._id', '=','sample_point_record._pid')
->leftJoin('animal_name a', function($join) {
$join->on(DB::raw("a.tid::VARCHAR"), DB::raw("="), DB::raw("sample_point_record.animal_name_i")) })
->leftJoin('geographic_unit g', function($join) {
$join->on(DB::raw("g.tid::VARCHAR"), DB::raw("="), DB::raw("sample_point_record.geography_unit_id")) })
$query->get();