英文:
Mysql query to laravel eloquent or query builder nested select
问题
以下是你要的翻译内容：
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
如何在 Laravel Eloquent 或查询构建器中执行这个操作？
英文:
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 ?
答案1
得分: 1
尝试这种方式，
我尝试了这种方法，它有效。但在这里，您可以将**->having()语句更改为havingRaw("'1' IS NOT NULL")**，或者我提供的代码也有效。
$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();
如果您想了解关于Laravel查询的信息，请参考文档。
或者，如果您需要动态查询，请尝试这种方式，
$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();
如果这不是您期望的答案，请留下评论，我将提供另一种方法来完成此操作：)
英文:
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 
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论