How to convert a raw sql query in Laravel 6?

Question

I have a query that works fine in Heidi SQL. Now I need to write the same query in Laravel. But it's not returning the exact result which I get in Heidi SQL. Someone help me here to update my query. Thanks in advance.

Query:

SELECT
    x.*,
    IFNULL(y.status_count, 0) 
FROM
    status x
LEFT JOIN (
    SELECT
        order_status,
        COUNT(id) AS status_count 
    FROM
        order_header
    WHERE
        user_id = 1
    GROUP BY
        order_status
) AS y
    ON x.code = y.order_status
WHERE
    x.type = 'ORD'

What I've written in Laravel:

$orderStatistics = OrderHeader::select(
        'status.id',
        'status.name',
        'status.description',
        'status.type',
        'status.code',                                                    
        DB::raw('order_status,count(*) as status_count'),
        DB::raw('IFNULL(`order_header`.`order_status`, 0)')
    )
    ->leftjoin('status', 'status.code', '=', 'order_header.order_status')
    ->orderBy('status.id')
    ->groupBy('order_status')
    ->where([
        ['order_header.user_id', $userID],
        ['status.type', 'ORD']
    ])
    ->get();

$userID is assigned separately. What I need to return is if any order_status not found for the given user id in the order_header table to display that order_status count as 0.

Currently, I get statuses for the given user id, I need to display all the statuses from the status table and the count as 0.

Answer 1

This is the exact same query. I haven't tested it myself but this is the syntax shown in the documentation.

$y = DB::table('order_header')
    ->select('order_status')
    ->selectRaw('COUNT(id) AS status_count')
    ->where('user_id', 1)
    ->groupBy('order_status');

$query = DB::table('status', 'x')
    ->select('x.*')
    ->selectRaw('IFNULL(y.status_count, 0) AS status_count')
    ->leftJoinSub($y, 'y', function ($join) {
        $join->on('x.code', 'y.order_status');
    })
    ->where('x.type', 'ORD')
    ->get();