Eloquent - Join a Subquery-CodePudding

I have a complicated query that I would like to translate either in Eloquent ORM or with the Query Builder for a Laravel site, but I can't do it, can someone help me? Here is my SQL query

SELECT opp_id, risk_study.rst_id, risk_study.rst_status
FROM opportunity
         LEFT JOIN risk_study ON risk_study.rst_id =
             (SELECT risk_study_quote_vehicle.rst_id
              FROM risk_study_quote_vehicle
                       INNER JOIN quote_vehicle ON quote_vehicle.quv_id = risk_study_quote_vehicle.quv_id
                       INNER JOIN quote ON quote.quo_id = quote_vehicle.quo_id
              WHERE quote.opp_id = opportunity.opp_id
              ORDER BY risk_study_quote_vehicle.rst_id DESC
              LIMIT 0,1)
WHERE 1 = 1
  AND opportunity.per_id_process = '5'
  AND opportunity.opp_locked = '1'

Here is my solution. But this query does not retrieve values from the risk_study table.

$opportunities = Opportunity::select('opp_id', 'risk_study.rst_id', 'risk_study.rst_status')
    ->leftJoin('risk_study', function ($join) {
        $join->on('risk_study.rst_id', '=', DB::raw('"SELECT risk_study_quote_vehicle.rst_id FROM risk_study_quote_vehicle INNER JOIN quote_vehicle ON quote_vehicle.quv_id = risk_study_quote_vehicle.quv_id INNER JOIN quote ON quote.quo_id = quote_vehicle.quo_id WHERE quote.opp_id = opportunity.opp_id ORDER BY risk_study_quote_vehicle.rst_id DESC LIMIT 0,1"'));
    })
    ->where([
        ['per_id_process', 5],
        ['opp_locked', 1],
    ])
    ->get();

CodePudding user response：

Finally, I wrote the query and it works ! Thanks !

        $opportunities = Opportunity::query()
        ->select(
            'opp_id',
            'risk_study.rst_id',
            'risk_study.rst_status'
        )
        ->from('opportunity')
        ->leftJoin('risk_study', function ($join) {
            $join->where('risk_study.rst_id', function ($sub)  {
                $sub->select('risk_study_quote_vehicle.rst_id')
                    ->from('risk_study_quote_vehicle')
                    ->join('quote_vehicle', 'quote_vehicle.quv_id', 'risk_study_quote_vehicle.quv_id')
                    ->join('quote', 'quote.quo_id', 'quote_vehicle.quo_id')
                    ->whereColumn('quote.opp_id', 'opportunity.opp_id')
                    ->orderByDesc('risk_study_quote_vehicle.rst_id')
                    ->limit(1);
            });
        })
        ->where('opportunity.per_id_process', '5')
        ->where('opportunity.opp_locked', '1')
        ->get();

CodePudding user response：

You should be able to use a Closure as the second parameter of where() inside your join Closure.

I don't think Laravel's query builder supports the LIMIT 0,1 statement.

I don't know why you need WHERE 1 = 1, but you should be able to use whereRaw for that.

When you're done, the result looks very alike a formatted SQL query.

$opportunities = Opportunity::query()
    ->select(
        'opp_id',
        'risk_study.rst_id',
        'risk_study.rst_status'
    )
    ->from('opportunity')
    ->leftJoin('risk_study', function ($join) {
        $join->where('risk_study_quote_vehicle.rst_id', function ($sub) {
            $sub->select('risk_study_quote_vehicle.rst_id')
                ->from('risk_study_quote_vehicle')
                ->join('quote_vehicle', 'quote_vehicle.quv.id', 'risk_study_quote_vehicle.quv_id')
                ->join('quote', 'quote.quo_id', 'quote_vehicle.quo_id')
                ->where('quote_opp_id', 'opportunity.opp_id')
                ->orderByDesc('risk_study_quote_vehicle.rst_id')
                ->limit(1);
        });
    })
    ->whereRaw('1 = 1')
    ->where('opportunity.per_id_process', '5')
    ->where('opportunity.opp_locked', '1')
    ->get();