Relationship query orWhere overwrites other wheres

Question

Case

Survey based on a few questions which returns x amount of apartments based on the results.

An apartment can have a static price (price), or a price range (price_min and price_max) - not all apartments have a static price yet so they are being defined by the price range (for example 900-1000 monthly rent)

Problem

Without the orWhere([['price_min', '!=', null], ['price_max', '!=', null], ['price_min', '<', '900']] everything works fine. Only the apartments which get past the where conditions are being returned; although when the orWhere condition is being added all apartments with a price range (so no price) are being returned, no matter the previous conditions such as floor or sun

Code

$selectedType = Type::where('slug', '=', $slug)->where('type', '=', 'studio')->with(['apartments' => function ($query) use ($request) {
    $query->where('status', '!=', 'sold');

    if ($request->floor == 'low') {
        $query->where('floor', '<', '5');
    } elseif ($request->floor == 'mid') {
        $query->where('floor', '>', '1')->where('floor', '<', '6');
    } elseif ($request->floor == 'high') {
        $query->where('floor', '>', '4');
    }

    if ($request->sun == 'morning_sun') {
        $query->where('sun', '=', 'morning');
    } elseif ($request->sun == 'evening_sun') {
        $query->where('sun', '=', 'evening');
    }

    if ($request->price == '1') {
        $query->where('price', '<', '900')->orWhere([['price_min', '!=', null], ['price_max', '!=', null], ['price_min', '<', '900']]);
    } elseif ($request->price == '2') {
        $query->where('price', '<', '999')->orWhere([['price_min', '!=', null], ['price_max', '!=', null], ['price_min', '<', '999']]);
    }
}])->first();

Answer 1

You need to group the orWhere conditions. The following should do the trick:

if ($request->price == '1') {
    $query->where(function($q) {
      $q->where('price', '<', '900')->orWhere([['price_min', '!=', null], ['price_max', '!=', null], ['price_min', '<', '900']]);
    });
} elseif ($request->price == '2') {
    $query->where(function($q) {
      $q->where('price', '<', '999')->orWhere([['price_min', '!=', null], ['price_max', '!=', null], ['price_min', '<', '999']]);
    });
}