Vfrdtyky

I am trying to convert one of my MySQL query to Laravel Query Builder. I tried some of the methods but seems like I am not able to achieve the same results. The query is mentioned below.

For reference I am using Laravel 5.3 and planning to upgrade it soon.

SELECT eac.id,eac.gender,eac.start_date,eac.end_date,eac.ad_image_path,eac.ad_link,eac.requestfrom,eac.traffic,eac.registertype,eacr.region_id,eac.active,eac.impression,eac.center_image_path,eac.bottom_image_path,eac.approved_by,er.name as country_name,eac.budget,sum(budget/ (DATEDIFF(end_date,start_date)) *1000) as daily_imp ,eaa.impression_count,eac.customer_id,eaa.created_at



    FROM  advert_customer eac



    JOIN  advert_customer_regions eacr ON eac.id = eacr.advert_customer_id



    JOIN  regions er ON er.id = eacr.region_id



    LEFT JOIN  advert_abstract eaa on eac.id = eaa.advert_customer_id



    WHERE eac.requestfrom ='web' AND eac.registertype = 'paid' AND eac.active = 1 AND eac.approved_by = 1 AND eac.gender ='male' AND er.name = 'india' AND IF((SELECT CASE WHEN DATE(eaa.created_at) = DATE(CURDATE()) THEN eaa.created_at ELSE NULL END), eaa.created_at , NULL)



    group by eac.id,eac.gender,eac.start_date,eac.end_date,eac.ad_image_path,eac.ad_link,eac.requestfrom,eac.traffic,eac.registertype,eacr.region_id,eac.active,eac.impression,eac.center_image_path,eac.bottom_image_path,eac.approved_by,er.name,eac.budget ,eaa.impression_count,eac.customer_id, eaa.created_at

I have tried this query builder method, but seems like there is something wrong about it. I am not sure where is the mistake any guidance will be helpful.

DB::table('advert_customer as eac')->select("eac.id","eac.gender","eac.start_date","eac.end_date","eac.ad_image_path","eac.ad_link","eac.requestfrom","eac.traffic","eac.position","eac.registertype","eacr.region_id","eac.active","eac.impression","eac.center_image_path","eac.bottom_image_path","eac.approved_by","er.name as country_name","eac.budget",DB::raw("sum(budget/ (DATEDIFF(end_date,start_date)) *1000) as daily_imp"),"eaa.impression_count","eac.customer_id", "eaa.created_at")

                            ->JOIN('advert_customer_regions as eacr','eac.id','=','eacr.advert_customer_id') 

                            ->JOIN('regions as er','er.id','=','eacr.region_id')

                            ->LEFTJOIN('advert_abstract as eaa','eac.id','=','eaa.advert_customer_id') 



                            ->where('er.name', $event->country == null ? "India" : $event->country)

                            ->where('eac.requestfrom','web')

                            ->where('eac.registertype','paid')

                            ->where('eac.active',1)

                            ->where('eac.approved_by',1)



                            ->orwhere("IF((SELECT CASE WHEN DATE('eaa.created_at') = DATE(CURDATE()) THEN 'eaa.created_at' ELSE NULL END), 'eaa.created_at' , NULL)")







                            ->where(function($q){

                                $q->whereDate('eac.start_date','<=',Carbon::today())->where('eac.end_date','>=',Carbon::now());

                            })

                            ->where(function($q){

                                $q->where("eac.gender", "all");

                            })

                            ->groupBy("eac.id","eac.gender","eac.start_date","eac.end_date","eac.ad_image_path","eac.ad_link","eac.requestfrom","eac.traffic","eac.position","eac.registertype","eacr.region_id","eac.active","eac.impression","eac.center_image_path","eac.bottom_image_path","eac.approved_by","eac.budget","eaa.impression_count","eac.customer_id","eaa.created_at")

                            ->inRandomOrder()

                            ->take(5)

                            ->get();

Thank you (In advance)