I have this query:

    $result = PortingItem::whereHas('porting', function ($query) {
        $query->whereIn('status', [
            Porting::STATUS_REQUESTED,
            Porting::STATUS_ACCEPTED,
            Porting::STATUS_DELAYED,
        ]);
    })->where(function ($query) use ($numbers) {
        $query->whereBetween('phone_number_start', [$numbers[0], $numbers[1]])
            ->orWhereBetween('phone_number_end', [$numbers[0], $numbers[1]]);
    })->orWhere([
        ['phone_number_start', '<=', $numbers[0]],
        ['phone_number_end', '>=', $numbers[1]],
    ])->get();

The PortingItem model still returns the query result while the porting relation is empty. I don't understand why this happens.

This is my Porting model relation

    public function items()
    {
        return $this->hasMany(PortingItem::class);
    }

This is my PortingItem model relation:

    public function porting()
    {
        return $this->belongsTo(Porting::class);
    }

CodePudding user response：

You should always group orWhere calls in order to avoid unexpected behavior when global scopes are applied.

$result = PortingItem::whereHas('porting', function ($query) {
        $query->whereIn('status', [
            Porting::STATUS_REQUESTED,
            Porting::STATUS_ACCEPTED,
            Porting::STATUS_DELAYED,
        ]);
    })->where(function ($query) use ($numbers) {
        $query->where(function ($query) use ($numbers) {
            $query->whereBetween('phone_number_start', [$numbers[0], $numbers[1]])
                ->orWhereBetween('phone_number_end', [$numbers[0], $numbers[1]]);
        })->orWhere(function ($query) use ($numbers) {
            $query->where('phone_number_start', '<=', $numbers[0])
                ->where('phone_number_end', '>=', $numbers[1]);
        });
    })->get();

https://laravel.com/docs/8.x/queries#logical-grouping