jeudi 14 septembre 2017

Eloquent Query going wrong

Hi All i am trying to generate an eloquent query which has to be equal to below raw query but somewhere i am missing something and not getting brackets for the first where clause.

select `smd`.`id` 
from `smd` 
inner join `ac` on `t1`.`channel_id` = `ac`.`id` 
inner join `acm` on `ac`.`id` = `acm`.`channel_id` 
where ((`smd`.`start_date` <= '2017-09-18 00:00:00' and `smd`.`end_date` >= '2017-09-18 00:00:00') 
or (`smd`.`start_date` >= '2017-09-18 00:00:00' and `smd`.`end_date` <= '2017-09-20 23:59:59') 
or (`smd`.`start_date` <= '2017-09-20 23:59:59' and `smd`.`end_date` >= '2017-09-20 23:59:59')) 
and `smd`.`advertiser_id` = 42 and `smd`.`is_deleted` = 0 
and `ac`.`is_enabled` = 1 and `acm`.`is_offline` = 1

But the query which i am getting is below one from the eloquent

select `smd`.`id` 
from `smd` 
inner join `ac` on `t1`.`channel_id` = `ac`.`id` 
inner join `acm` on `ac`.`id` = `acm`.`channel_id` 
where (`smd`.`start_date` <= '2017-09-18 00:00:00' and `smd`.`end_date` >= '2017-09-18 00:00:00') 
or (`smd`.`start_date` >= '2017-09-18 00:00:00' and `smd`.`end_date` <= '2017-09-20 23:59:59') 
or (`smd`.`start_date` <= '2017-09-20 23:59:59' and `smd`.`end_date` >= '2017-09-20 23:59:59') 
and `smd`.`advertiser_id` = 42 and `smd`.`is_deleted` = 0 
and `ac`.`is_enabled` = 1 and `acm`.`is_offline` = 1

and equivalent Eloquent query i have written is below one

$deals = self::join('ac', 'smd.channel_id', '=', 'ac.id')
        ->join('acm', 'ac.id', '=', 'acm.channel_id')
        ->where(function ($q) use($startDate, $endDate) {

            $q->Where('smd.start_date', '<=', $startDate)
                ->where('smd.end_date', '>=', $startDate);
        })->orWhere(function ($q) use($startDate, $endDate) {
            $q->Where('smd.start_date', '>=', $startDate)
                ->where('smd.end_date', '<=', $endDate);
        })->orWhere(function ($q) use($startDate, $endDate) {
            $q->Where('smd.start_date', '<=', $endDate)
                ->where('smd.end_date', '>=', $endDate);
        })->where('smd.advertiser_id', $advertiserId)
        ->where('smd.is_deleted', 0)
        ->where('ac.is_enabled', 1)
        ->where('acm.is_offline', $channelGroupType);

    if($channelGroupType === 0 && $restOfTheChannelDeals === 0){
        $deals->whereIn('smd.channel_id', $channelIds);
    }else if($channelGroupType === 0 && $restOfTheChannelDeals){
        $deals->whereNotIn('smd.channel_id', $channelIds);
    }
    $deals->select('smd.id')->get();

Note::Assume all the parameters are given



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire