How to implement a complex query in eloquent?

Question

MySQL

Hi all!
Recently I started learning laravel, I decided to implement the current task - a small advertising account - but there was a problem with building a request in eloquent ( here colleagues helped to make a raw request)
Actually, the essence of the question is: how to feed this in laravel:

SELECT ads.*, COUNT(DISTINCT clicks.id) as clicks, COUNT(DISTINCT shows.id) as shows, (COUNT(DISTINCT clicks.id) * COUNT(DISTINCT shows.id))/100 as CTR
FROM ads
LEFT JOIN shows ON ads.id = shows.ad_id AND DATE(shows.created_at) = '2014-02-24'
LEFT JOIN clicks ON ads.id = clicks.ad_id AND DATE(clicks.created_at) = '2014-02-24'
GROUP BY ads.id;

Where date is a parameter.
Thank you all for the tips, hints and solutions.

Answer 1

$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');

Counts views, clicks, ctr and returns info on platforms, devices.

Answer 2

SELECT ads.*, COUNT(DISTINCT clicks.id) as clicks, COUNT(DISTINCT shows.id) as shows, (COUNT(DISTINCT clicks.id) * COUNT(DISTINCT shows.id))/100 as CTR
FROM ads
LEFT JOIN shows ON ads.id = shows.ad_id
LEFT JOIN clicks ON ads.id = clicks.ad_id AND shows.created_at = clicks.created_at
WHERE shows.created_at = '2014-02-24'
GROUP BY ads.id

I would do so. What is the problem in laravel? how did you try?
AND in the second JOIN can be removed in WHERE