Laravel Query With Join to Select With Other Join

Question

How to Convert SQL With Join Select Have Another Join And Where, I have Query Like This :

SELECT A.prs_id_sales,B.namesls,COUNT( A.prs_id ) AS pros,
IFNULL( C.drive, 0 ) drive,
IFNULL( C.spkdrive, 0 ) spkdrive,
SUM(IF((A.prs_spk_no <> '' ), 1, 0 )) spk 
FROM `prosys_prospect` AS A INNER JOIN prosys_sls AS B ON A.prs_id_sales = B.nipsls
LEFT JOIN (
        SELECT A.prs_id_sales, COUNT( C.v_id ) drive,
        SUM(IF(( A.prs_spk_no <> '' ), 1, 0 )) spkdrive 
        FROM `prosys_prospect` AS A 
        LEFT JOIN prosys_visit AS C ON A.prs_id_prospect =  C.v_id_prospect
        WHERE C.v_testdrive = '1'
        AND C.v_visit_number = '1'
        AND A.prs_kdcab = 'PK3A'
        AND DATE(prs_create_entry) BETWEEN '2023-01-01' AND '2023-01-02'
        GROUP BY A.prs_id_sales 
) AS C ON A.prs_id_sales = C.prs_id_sales
WHERE B.expired = '0' 
AND A.prs_kdcab = 'PK3A'
AND DATE(prs_create_entry) BETWEEN '2023-01-01' AND '2023-01-02'
GROUP BY B.nipsls 
ORDER BY B.namesls

I'am Following This Question

But This Not Working With My Case,

Here My Complete Wrong Query Builder

score 0 · Accepted Answer · answered Mar 03 '23 at 04:19

I have shared the eloquent query based on your SQL query. Kindly please check below example :

Initialize the DB at top of the file.

use Illuminate\Support\Facades\DB

DB::table('prosys_prospect as A')
    ->select('A.prs_id_sales', 'B.namesls', DB::raw('COUNT(A.prs_id) AS pros'), 
            DB::raw('IFNULL(C.drive, 0) AS drive'), DB::raw('IFNULL(C.spkdrive, 0) AS spkdrive'), 
            DB::raw('SUM(IF((A.prs_spk_no <> ""), 1, 0 )) AS spk'))
    ->join('prosys_sls AS B', 'A.prs_id_sales', '=', 'B.nipsls')
    ->leftJoin(DB::raw('(SELECT A.prs_id_sales, COUNT(C.v_id) AS drive,
                    SUM(IF((A.prs_spk_no <> ""), 1, 0 )) AS spkdrive 
                    FROM `prosys_prospect` AS A 
                    LEFT JOIN prosys_visit AS C ON A.prs_id_prospect = C.v_id_prospect
                    WHERE C.v_testdrive = "1"
                    AND C.v_visit_number = "1"
                    AND A.prs_kdcab = "PK3A"
                    AND DATE(prs_create_entry) BETWEEN "2023-01-01" AND "2023-01-02"
                    GROUP BY A.prs_id_sales) AS C'), 'A.prs_id_sales', '=', 'C.prs_id_sales')
    ->where('B.expired', '=', '0')
    ->where('A.prs_kdcab', '=', 'PK3A')
    ->whereBetween(DB::raw('DATE(prs_create_entry)'), ['2023-01-01', '2023-01-02'])
    ->groupBy('B.nipsls')
    ->orderBy('B.namesls')
    ->get();

Hope this will help you.

Laravel Query With Join to Select With Other Join

1 Answers1