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_offerFROM (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();Advertisement
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();