Laravel Eloquent Sub-Select and Aggregate SQL

I am stuck on this for months now and tried various Query Builds to get what I needed and failed miserably (i am not to laravel) until I sort of got some of my data which full fills all my criteria except one.. reaching this point and slowly giving up I thought I write the RAW SQL statement which full fills all my criteria and try to work backwards.

Basically I am returning a list of incomplete tasks which have to be performed today. A task is completed based on it's Schedule which defines how many times a certain tasks has to be performed for example: Brushing Teeth 3 x a Day

My closest eloquent build is the following - however it still displays completed tasks. Apart from that I have some 'magic numbers' in it because i wasn't able to reference them with the column in the database because of the whereHas implementation in laravel

$schedules = TaskSchedule::where(function($query){
        $day=Dayofweek::today()->id;
        $query->where('start_dayofweek_id','=',$day)
              ->orWhere('finishby_dayofweek_id','=',$day)
              ->orWhereRaw( $day.' BETWEEN start_dayofweek_id AND finishby_dayofweek_id');
    })->whereHas('logs',function($query){
        $yesterday = '\''.date('Y-m-d',strtotime("-1 day")).' 00:00:00.000\'';
        $today = '\''.date('Y-m-d',strtotime("+ 0 day")).' 23:59:59.999\'';
            $query->whereRaw('created_at BETWEEN '.$yesterday. ' AND '.$today);
        },'<',4)->OrderBy('finishby_dayofweek_id','asc')->get();

Which generates this SQL:

select * 
from [task_schedules] 
where ([start_dayofweek_id] = 3 or [finishby_dayofweek_id] = 3 or 
3 BETWEEN start_dayofweek_id AND finishby_dayofweek_id) and 
(
    select count(*) 
    from [cleaning_logs] 
    where [cleaning_logs].[task_schedule_id] = [task_schedules].[id] and 
        created_at BETWEEN '2016-03-08 00:00:00.000' AND '2016-03-09 23:59:59.999'
) < 4 
order by [finishby_dayofweek_id] asc

The SQL querry which I need to generate from eloquent would look exactly like:

SELECT *
    FROM task_schedules LEFT JOIN [cleaning_logs] ON [cleaning_logs].[task_schedule_id] = [task_schedules].[id]
    WHERE [task_schedules].[id] NOT IN ( 
            SELECT [cleaning_logs].[task_schedule_id] 
            FROM [cleaning_logs] where [cleaning_logs].[created_at] BETWEEN '2016-03-08 00:00:00.000' AND '2016-03-09 23:59:59.999' 
            GROUP BY [cleaning_logs].[task_schedule_id]  
    HAVING count([cleaning_logs].[task_schedule_id]) = [task_schedules].amount )

Of course the perfect answer would be a Eloquent Answer to the statement and explanation - however..

My questions:

1. How would I port an complex SQL statement like that into Eloquent?
2. Do I sort of work from the outside (outer SQL) to the inside (Sub-Query)?
3. Best practice with complex SQL querries? Send as RAW SQL? Stored function/procedure through migrations in laravel (is that even possible, how to call these then?)

via Chebli Mohamed