mysql - How to do in Laravel, subquery with calculated fields on SELECT?

Question

How can I make this query in Laravel eloquent. Please, no DB Record solution.

SELECT slo.batch, 
       slo.type, 
       (SELECT Count(sl1.id) 
        FROM   sync_log sl1 
        WHERE  sl1.status = 1 
               AND sl1.batch = slo.batch) AS success, 
       (SELECT Count(sl2.id) 
        FROM   sync_log sl2 
        WHERE  sl2.status = 0 
               AND sl2.batch = slo.batch) AS failed, 
       slo.created_at 
FROM   sync_log slo 
GROUP  BY batch 
ORDER  BY slo.created_at 

Below is the database table.

question from:https://stackoverflow.com/questions/65901805/how-to-do-in-laravel-subquery-with-calculated-fields-on-select

Answer 1

Try something like :

$result=DB::table('sync_log as slo')
    ->select('slo.batch','slo.type', 'slo.created_at', DB::raw(('SELECT Count(sl1.id) FROM sync_log sl1 WHERE sl1.status=1 AND sl1.batch = slo.batch) AS success'), DB::raw(('SELECT Count(sl2.id) FROM sync_log sl2 WHERE sl2.status = 0 AND sl2.batch = slo.batch) AS failed') 
    ->groupBy('batch')
    ->orderBy('slo.created_at')
    ->get();