How to write sub query's Laravel 5.1

Here is my MySql query which I want to write in to laravel 5.1.I want to write this query in laravel model but i stuck in sub query.Also I read tutorials and articles but it not working for me.

     SELECT
        e.id AS event_id,
       ea.event_id,
        ea.`status`,
        e.event_name,
      e.creator_id,
      e.venue,
        e.event_datetime,
    (SELECT profile_pic FROM users u1  WHERE u1.id = e.creator_id ) AS host_picture,
    (SELECT CONCAT(first_name,' ',last_name) FROM users u1  WHERE u1.id = e.creator_id ) AS host_name,
    (SELECT COUNT(cea.event_id) FROM event_attendee cea WHERE cea.event_id =  ea.event_id) as total_person,
    (SELECT COUNT(cea.event_id) FROM event_attendee cea WHERE cea.event_id =  ea.event_id AND cea.user_id IN (SELECT friend_id FROM user_friends WHERE user_id = 14 AND `status` = 1) ) as totalmyfriends,
    (SELECT COUNT(cea.event_id) FROM event_attendee cea WHERE cea.event_id =  ea.event_id AND cea.user_id IN (SELECT friend_id FROM user_friends WHERE user_id IN ( SELECT friend_id FROM user_friends WHERE user_id = 14 ) AND friend_id != 14) ) as friendsoffriends


    FROM
        event_attendee ea
    LEFT JOIN `event` e ON ea.event_id = e.id
    LEFT JOIN users u on ea.user_id = u.id

    WHERE u.id = 14

I have try below code but it didn't work.

$eventAtendeess = DB::table('event_attendee')
                    ->select('event.id as event_id','event_attendee.event_id','event_attendee.status','event.event_name','event.creator_id','event.venue','event.event_datetime',DB::raw('SELECT profile_pic FROM users  WHERE users.id = event.creator_id ) AS host_picture'))

                    ->leftjoin('event', 'event_attendee.event_id', '=', 'event.id')
                    ->leftjoin('users', 'event_attendee.user_id', '=', 'users.id')
                    ->where('users.id', $user_id)->get();

Thanx in advance.

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/1fubpDz
via IFTTT