converting raw sql query to a laravel eloquent model

I've been trying to convert the following query to an eloquent model query/raw database query. I have limited knowledge of laravel and I've come to a stop.

 select 
                status, 
                (SELECT count(*) FROM active_call_statuses WHERE active_call_id = 'CA3a77245ab0eac10f8cf3aa8e7c8f9a91') AS total 
            from 
                active_call_statuses
            left JOIN
                active_calls ON active_calls.parent_call_id = active_call_statuses.active_call_id
            where 
                status IN('in-progress', 'completed', 'ringing', 'answered', 'busy', 'failed', 'no-answer')
            and 
                active_calls.parent_call_id = 'CA3a77245ab0eac10f8cf3aa8e7c8f9a91'

the pupose of the query is to select all columns with the given call status and count the total of status entries associated to the current call via its id in a sub query.

The query does what it's supposed to in mysql as far as i can see but I dont know how to convert this to an eloquent query.

The active_call_statuses table and active_calls table are linked to eachother via a one-to-many relationship on parent_call_id in eloquent respectively like so.

class ActiveCall extends Model

{
    /**
     * @var mixed
     */
    public $timestamps = false;

    /**
 * @var array fillable properties
 */
protected $fillable = ['user_id', 'conference_id', 'parent_call_id'];

/**
 * @return mixed
 */
public function statuses()
{
    return $this->belongsToMany('app\ActiveCallStatus');
}
}



  class ActiveCallStatus extends Model
{
/**
 * @var bool timestamps enabled
 */
public $timestamps = false;

/**
 * @var array fillable properties
 */
protected $fillable = ['active_call_id', 'user_id', 'status'];

/**
 * @return mixed
 */
public function activeCall()
{
    return $this->belongsTo('app\ActiveCall');
}
}

I've tried wraping the query in a DB::select with a DB::raw, calling a table with the DB->table(...)->selectRaw(...) .etc method by binding the parameters to the selectRaw aliased with :id but everything resulted in database errors invalid parameter number or other errors.

This is my last attempt:

processedUsers = DB::table('active_call_statuses')->select(
                    DB::raw("
                        SELECT
                        user_id,
                        status,
                        (SELECT count(*) FROM active_call_statuses WHERE active_call_id = :id) AS total 
                    FROM 
                        active_call_statuses"),
                    ['id' => $activeCall->parent_call_id])
                ->whereIn('status',"('in-progress', 'completed', 'ringing', 'answered', 'busy', 'failed', 'no-answer')")
                ->where("active_calls.parent_call_id", $activeCall->parent_call_id);

which resulted in:

[2018-03-19 12:33:45] local.ERROR: Invalid argument supplied for foreach() {"exception":"[object] (ErrorException(code: 0): Invalid argument supplied for foreach() at C:\\wamp64\\www\\Stage\\LanthopusX\\voip\\vendor\\laravel\\framework\\src\\Illuminate\\Database\\Query\\Builder.php:763)

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