CODEWITHSUNDEEP
phpsqllaravel
Retaker
Retaker

Reputation: 17

Mysql query to laravel eloquent or query builder nested select

I got the following code that gonna return the sum of score if tgl is '2023-01-01' and then that return value will be selected again to not show any null that the query find

SELECT *
FROM (
    SELECT `kodeSales`,
        `departemenId`,
        `tgl`, 
        (SELECT SUM(CASE WHEN tgl IN ('2023-01-01') THEN score END)) AS '1'
    FROM `history_penjualan` 
    WHERE `tgl` BETWEEN '2023-01-01' AND '2023-01-30' 
        `departemenId` = '28' 
    GROUP BY `tgl`
) AS temp
WHERE '1' IS NOT NULL

How do I do this in laravel eloquent or query builder ?

Upvotes: 0

Views: 103

Answers (1)

Pathum Bandara
Pathum Bandara

Reputation: 363

Try this way,

I tried this and it works. but in here you can change the ->having() statement as havingRaw("'1' IS NOT NULL") or I provided code also works.

$sum_of_score = DB::table('history_penjualan')
        ->select('kodeSales', 'departemenId', 'tgl', DB::raw("SUM(CASE WHEN tgl = '2023-01-01' THEN score END) AS '1'"))
        ->whereBetween('tgl', ['2023-01-01', '2023-01-30'])
        ->where('departemenId', '=', 28)
        ->groupBy('tgl')
        ->having('1', '!=', null)
        ->get();

or if you need query in dynamic try this way,

$query = DB::table('history_penjualan')
        ->select(DB::raw('kodeSales, departemenId, tgl, SUM(CASE WHEN tgl = "2023-01-01" THEN score END) as "1"'))
        ->whereBetween('tgl', ['2023-01-01', '2023-01-30'])
        ->where('departemenId', '=', '28')
        ->groupBy('tgl');

$final_data = $query->having('1', '<>', null)->get();

If this is not your expected answer please leave a comment, I'll give another way to do this :)

Upvotes: 1

Related Questions