Laravel查询使用和

Question

我的Larvel查询有一个问题，它不能按需要工作。它应该过滤“战役”的属性团队，渠道，地区和时间段的开始和结束。这个时间段是没有问题的。

用户可以筛选不同的团队/通道/区域，并可以选择no、一个或多个属性，这些属性应该以或连接为或。

期望的结果应该给予所有的战役，其中有选定的队伍，渠道和地区。但目前，我得到的每一个战役，有选定的球队，或选定的渠道或选定的地区。

public function searchCampaigns($page, $limit, $data)
{
    $startDate = $data['startDate'];
    $endDate = $data['endDate'];

    $campaigns = Campaign::distinct()->select('campaigns.*')
                ->join('campaign_team', 'campaign_team.campaign_id', '=', 'campaigns.id')
                ->join('campaign_region', 'campaign_region.campaign_id', '=', 'campaigns.id')
                ->join('campaign_channel', 'campaign_channel.campaign_id', '=', 'campaigns.id')
                ->join('teams', 'campaign_team.team_id', '=', 'teams.id')
                ->join('regions', 'campaign_region.region_id', '=', 'regions.id')
                ->join('channels', 'campaign_channel.channel_id', '=', 'channels.id')
//Interesting Part
                ->whereIn('teams.id', $data['teams'])
                ->whereIn('regions.id', $data['regions'])
                ->whereIn('channels.id', $data['channels'])
                ->where(function ($query) use ($startDate, $endDate) {
                    $query->where('campaigns.start_date', '>=', $startDate)
                        ->where('campaigns.end_date', '<=', $endDate);
                })

                ->orWhere(function ($query) use ($startDate, $endDate) {
                    $query->where('campaigns.end_date', '<=', $endDate)
                        ->where('campaigns.end_date', '>=', $startDate);
                })

                ->orWhere(function ($query) use ($startDate, $endDate) {
                    $query->where('campaigns.start_date', '>=', $startDate)
                        ->where('campaigns.start_date', '<=', $endDate);
                })

                ->orWhere(function ($query) use ($startDate, $endDate) {
                    $query->where('campaigns.start_date', '<=', $startDate)
                        ->Where('campaigns.end_date', '>=', $endDate);
                })                    ->orderBy('start_date', 'asc');

    $result['count'] = sizeof($campaigns->lists('id'));
    $result['campaigns'] = $campaigns->skip($limit * ($page - 1))->take($limit)->get();

    return $result;
}

Answer 1

试试这个。它应该能工作。(如有任何问题发生，请通知我:)

public function searchCampaigns($page, $limit, $data)
{
    $result = [];     

    // Checking Validity of Start Date
    if (empty($data['startDate']) || (Carbon::createFromFormat('Y-m-d', $data['startDate']) === false))
        return $result;

    // Checking Validity of End Date                
    if (empty($data['endDate']) || (Carbon::createFromFormat('Y-m-d', $data['endDate']) === false))
        return $result;                 

    // Comparing Start & End Date
    $startDate = Carbon::parse($data['startDate']);
    $endDate = Carbon::parse($data['endDate']);

    if($startDate->gt($endDate))
        return $result;

    if($endDate->lt($startDate))
        return $result;

    $startDate = $data['startDate'];
    $endDate = $data['endDate'];                        

    $campaigns = Campaign::distinct()->select('campaigns.*')
                ->join('campaign_team', 'campaign_team.campaign_id', '=', 'campaigns.id')
                ->join('campaign_region', 'campaign_region.campaign_id', '=', 'campaigns.id')
                ->join('campaign_channel', 'campaign_channel.campaign_id', '=', 'campaigns.id')
                ->join('teams', 'campaign_team.team_id', '=', 'teams.id')
                ->join('regions', 'campaign_region.region_id', '=', 'regions.id')
                ->join('channels', 'campaign_channel.channel_id', '=', 'channels.id')
                ->whereIn('teams.id', $data['teams'])
                ->whereIn('regions.id', $data['regions'])
                ->whereIn('channels.id', $data['channels'])
                ->where('campaigns.start_date', '>=', $startDate)
                ->where('campaigns.end_date', '<=', $endDate);
                ->orderBy('start_date', 'asc');

    $result['count'] = sizeof($campaigns->lists('id'));
    $result['campaigns'] = $campaigns->skip($limit * ($page - 1))->take($limit)->get();

    return $result;
}