Mysql query to laravel eloquent or query builder nested select

Question

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 ?

I have add an answer check it. If you want to convert more queries try this website. sql2builder.github.io — Pathum Bandara
– Pathum Bandara, Commented Feb 10, 2023 at 5:31

Pathum Bandara · Accepted Answer · 2023-02-10 06:52:09Z

1

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

If you want to learn about Laravel queries refer the document.
https://laravel.com/docs/9.x/queries

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 :)

edited Feb 10, 2023 at 6:52

answered Feb 10, 2023 at 3:43

Pathum Bandara

3632 gold badges3 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Retaker Over a year ago

I kinda need what i ask because the query will be dynamic i will add a ton of SUM and Having and/or Where Clause

Pathum Bandara Over a year ago

OK, I have edited my answer check it bro.

Collectives™ on Stack Overflow

Mysql query to laravel eloquent or query builder nested select

1 Answer 1

Try this way,

or if you need query in dynamic try this way,

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Try this way,

or if you need query in dynamic try this way,

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related