find_in_set within left join in Laravel

2019-06-06 03:53发布

问题:

How can I use the find_in_set() with laravel query builder. Here is my raw query:

SELECT *
FROM table1 as t1
  LEFT JOIN table2 as t2 ON find_in_set(t2.country, t1.fk_country_id)

回答1:

You can use DB::raw like as

DB::table('table1')->leftJoin('table2', function($join){
   $join->on(DB::raw("find_in_set(table2.country, table1.fk_country_id)",DB::raw(''),DB::raw('')));
});


回答2:

you can use DB:raw as in

    DB::table('table1')->leftJoin('table2', function($join){
        $join->on(DB::raw("find_in_set(table2.country, table1.fk_country_id)"));
    });

===================

Edit : Uchiha answer is the accurate one, since laravel "on" requires 3 arguments: a field, operator,field . i.e on('table1.id','=','table2.id')



回答3:

Using $join->on( was generating an incorrect query in my case (Laravel 5.4).

The incorrect SQL generated was like t1 left join t2 on find_in_set(t2.tag, t1.tags) = where t1.foo..

Here, whereRaw worked for me

DB::table('table1')->leftJoin('table2', function($query) {
    $query->whereRaw("find_in_set(...)");
})

Reference: From issue referring Laravel 5.5 (https://github.com/laravel/framework/issues/23488)