$query = Ad::
select(array('ads.*', DB::raw('COUNT(DISTINCT clicks.id) as clicks_count'), DB::raw('COUNT(DISTINCT shows.id) as shows_count'), DB::raw('(COUNT(DISTINCT clicks.id) * COUNT(DISTINCT shows.id))/100 as CTR')))
->leftJoin('clicks', function($join) use($date){
$join->on('ads.id', '=', 'clicks.ad_id')->where(DB::raw('DATE(clicks.created_at)'), '=', $date);
})
->leftJoin('shows', function($join) use($date){
$join->on('ads.id', '=', 'shows.ad_id')->where(DB::raw('DATE(shows.created_at)'), '=', $date);
})
->groupBy('ads.id')->with('devices', 'platforms');
Подсчитывает просмотры, клики, цтр и возвращает инфу по платформам, девайсам.
