How to convert sql query into laravel query builder ?

How about using a raw query ? These types of queries (inner joins, subqueries etc.) are not normally used in the ORM.

$query = "SELECT u.`name`, u.`email`, uf.`flight_no`, uf.`pick_up`, uf.`arrival_date`, (SELECT name FROM guides INNER JOIN users_agency ua ON guides.`id` = ua.`guide_id` WHERE ua.`user_id` = u.`id`) as guide_name, (SELECT mobile FROM guides INNER JOIN users_agency ua ON guides.`id` = ua.`guide_id` WHERE ua.`user_id` = u.`id`) as guide_mobile, 
               uf.`arrival_time` FROM users AS u
               INNER JOIN users_flights AS uf
               ON u.`id` = uf.`user_id`
               WHERE uf.`pick_up` = '1'
               AND uf.`arrival_date` = CURDATE() + 1
               AND u.id NOT IN (SELECT user_id FROM role_user)
               ORDER BY u.id DESC";

$results = DB::select(DB::raw($query));
dd($results);

i have already used this one.

And what would you like to replace it with ?

You can run queries using the DB facade. The DB facade provides methods for each type of query: select, update, insert, delete and statement.