How to write Joins inside Left join in Laravel-CodePudding

I have a query which has sub query with 2 joins inside a Left join and I was trying to convert it to Laravel

LEFT JOIN (
    orders xo
    JOIN factories xs
        ON ( xs.factory_id = xo.factory_id )
    JOIN setup sp
        ON ( sp.factory_id = xs.legacy_factory_id)
    )
    ON ( xo.production_id = po.production_id )

I tried something like this

->leftJoin('orders AS xo', function ($query) use ($input) {
                        $query->join('factories AS xs','xs.factory_id','=','xo.factory_id')
                        ->join('setup AS sp','sp.factory_id','=','xs.legacy_factory_id');
                                
                    },function($join) {
            $join->on('xo.production_id','=','po.production_id');

Would like some help with this convertion

CodePudding user response：

There was a problem that you do not want to do Eloquent: Relationships? look here https://laravel.com/docs/9.x/eloquent-relationships#main-content You will need a patch for this problem hasOne or hasMany

CodePudding user response：

You can use Subquery Joins like so

    $SubQuery= DB::table('orders AS xo')
    ->join('factories AS xs','xs.factory_id','=','xo.factory_id')
    ->join('setup AS sp','sp.factory_id','=','xs.legacy_factory_id');

   // then use the subQuery like below:
// here a is the alias to the subquery
    DB::table('your_table as po')
    ->leftjoinSub($SubQuery, 'a', function ($join) { $join->on('a.production_id', '=', 'po.production_id'); })