how to convert SQL query in Laravel Query Builder

Question

SQL Query is

SELECT 
    *,
    (
        SELECT COUNT(*)
        FROM `users_data`
        WHERE `users_data`.`role` = `user_role`.`role_id`
    ) AS `total_users`
FROM `user_role`
WHERE `user_role`.`role_type` = 'USER'
ORDER BY `user_role`.`role_order_by` ASC

Answer 1

Use selectSub($query, $as) for your subquery in the SELECT clause.

Use selectRaw or DB::raw to use aggregate functions in the SELECT clause.

Use whereColumn($first, $second) to compare two columns.

The rest is pretty straightforward.

$results = DB::query()
    ->select('*')
    ->selectSub(
        function ($query) {
            return $query->selectRaw('count(*)')
                ->from('users_data')
                ->whereColumn('users_data.role', 'user_role.role_id');
        },
        'total_users'
    )
    ->from('user_role')
    ->where('user_role.role_type', 'USER')
    ->orderBy('user_role.role_order_by')
    ->get();

Or

// subquery
$query = DB::query()
    ->selectRaw('count(*)')
    ->from('users_data')
    ->whereColumn('users_data.role', 'user_role.role_id');

$results = DB::query()
    ->select('*')
    ->selectSub($query, 'total_users')
    ->from('user_role')
    ->where('user_role.role_type', 'USER')
    ->orderBy('user_role.role_order_by')
    ->get();