Conditional wherehas with Laravel Eloquent

Question

I have one similar (area) value in two tables one and two and these two tables has relation with main table master. At a time, the master table will be having data in one relation only and the other one will be null.

With this architecture, I have a search page where user can search any values related to these tables and the search fields are placed with AND condition.

Here, if user enters some value for area I need to check the area value exists in any one of the tables (one or two) without breaking the AND condition. Tried the below code but it is breaking AND rule and considering OR. Any suggestions to fix?

$result =  Master::where(function ($query) use ($request) {
    if ($request->filter == true) {
        $query->where('user_id', Auth::user()->id);
    }
    // other conditions here
    if (!empty($request->area_from) && !empty($request->area_to)) {
        $query->whereHas('one', function ($query) use ($request) {
            $query->whereBetween('area', [$request->area_from, $request->area_to]);
        });
        $query->orWhereHas('two', function ($query) use ($request) {
            $query->whereBetween('area', [$request->area_from, $request->area_to]);
        });
    }
    // other conditions here

})->with(['one', 'two'])->paginate($request->item);

I don't really understand what you mean with breaking the `AND`. — IGP, Aug 23 '19 at 03:54
@IGP All the search fields should be separated with `AND`. But here because of the `orWhereHas`, the condition is taking everything from that filed without considering previous conditions. — Anshad Vattapoyil, Aug 23 '19 at 03:57
OH! Just put another where closure encapsulating both whereHas — IGP, Aug 23 '19 at 04:01

score 1 · Answer 1 · answered Aug 23 '19 at 03:57

You are wrapping all of your where statements in brackets. I think what you want to do is pull your first part of the query out of the where clause so that you can easily wrap the whereHas part in brackets.

// Initialise the model
$query = new Master;

// Start building the query
if ($request->filter == true) {
    $query->where('user_id', Auth::user()->id);
}

if (!empty($request->area_from) && !empty($request->area_to)) {
    // Wrap these in brackets so we don't interfare with the previous where
    $query->where(function($query2) use ($request) {
        $query2->whereHas('one', function ($query3) use ($request) {
            $query3->whereBetween('area', [$request->area_from, $request->area_to]);
        });
        $query2->orWhereHas('two', function ($query3) use ($request) {
            $query3->whereBetween('area', [$request->area_from, $request->area_to]);
        });
    }
}

$query->with(['one', 'two'])->paginate($request->item);

score 1 · Answer 2 · answered Aug 23 '19 at 09:30

You can create a merged relationship refer this link

public function mergedOneAndTwo($value)
{
    // There two calls return collections
    // as defined in relations.
    $onedata= $this->one;
    $twodata= $this->two;

    // Merge collections and return single collection.
    return $onedata->merge($twodata);
}

and use whereHas('mergedOneAndTwo')

score 0 · Answer 3 · answered Aug 23 '19 at 12:17

Using a closer where and making the conditional inside may work fine

$master = Master::with('one')->with('two');
$result = $master->where(function($subQuery)
{   
    $subQuery->whereHas('one', function ( $query ) {
        $query->whereBetween('area', [$request->area_from, $request->area_to] ); //assuming  $request->area_from, $request->area_to is range of value
    })
    ->orWhereHas('two', function ( $query ) {
        $query->whereBetween('area', [$request->area_from, $request->area_to] );
    });
});

Conditional wherehas with Laravel Eloquent

3 Answers3