Laravel postgresql 加入错误。 "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();