How to select from two selected tables in Laravel Query Builder?

I want to convert my SQL to Laravel Query Builder version, however I can’t do that. Can anyone help me to solve this case or give me some advices, thank you.

Original SQL that work fine in mysql server.

SELECT Ifnull(no_of_accepted_job_offer, 0) AS no_of_accepted_job_offer
FROM   (SELECT job_name AS offer_job
        FROM   portal_setting) AS T1
       LEFT JOIN (SELECT Count(job_master.appno) AS no_of_accepted_job_offer,
                         job_master.offer_job
                  FROM   job_master
                         JOIN job_apply
                           ON job_master.appno = job_apply.appno
                  WHERE  job_apply.year = '2021'
                         AND job_master.status = 'accept'
                  GROUP  BY job_master.offer_job) AS T2
              ON T1.offer_job = T2.offer_job; 

Converted the original SQL to Laravel Query Builder, but it doesn’t work.

Error: SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘(select COUNT(job_master.appno)’ doesn’t exist

DB::table(DB::raw('(SELECT job_name AS offer_job
        FROM   portal_setting) AS T1'))
->select(DB::raw('Ifnull(no_of_accepted_job_offer,0) AS no_of_accepted_job_offer'))
->leftJoin('(SELECT Count(job_master.appno) AS no_of_accepted_job_offer,
                         job_master.offer_job
                  FROM   job_master
                         JOIN job_apply
                           ON job_master.appno = job_apply.appno
                  WHERE  job_apply.year = '2021'
                         AND job_master.status = 'accept'
                  GROUP  BY job_master.offer_job) AS T2','T1.offer_job','=','T2.offer_job')
->get();

Answer

DB::table doesn’t really work with subqueries.

But you can use fromSub and joinSub. These will take closures or query builder queries to be able to define your subqueries.

You can also access the query builder using DB::query() since you can’t use a specific table.

You’ll find each line of sql could be represented by a function in the query builder. It is worth looking through the documentation, or even the source code just to find all the functions.

So I came up with something like this:

$joinSubQuery = DB::table('job_master')
    ->join('job_apply', 'job_master.appno', '=', 'job_apply.appno')
    ->where('job_apply.year', '2021')
    ->where('job_master.status', 'accept')
    ->groupBy('job_master.offer_job')
    ->select('job_master.offer_job')
    ->selectRaw('count(job_master.appno as no_of_accepted_job_offer');


DB::query()
    ->fromSub(function($query) {
        $query->from('portal_setting')
            ->select('job_name as offer_job');
    }, 'T1')
    ->leftJoinSub($joinSubQuery, 'T2', function($join) {
        $join->on('T1.offer_job', '=', 'T2.offer_job')
    })
    ->selectRaw('Ifnull(no_of_accepted_job_offer, 0) AS no_of_accepted_job_offer')
    ->get();