Laravel eloquent relation search not working properly

Question

I am new in laravel and facing issue with eloquent search query.I have two table assets and assets_maintenance. They have relation with them which is assets hasMany assets_maintenance in asset Model assets belongsTo assets_maintenance in asset_maintenance model when user search then I put the following code to fetch results.

$data['reports'] = AssetMaintenance::with(['assets'])
    ->where('inspector_id',$tenant_inspector_id)
    ->where('maintenance_due_date','>=',Carbon::now()->startOfDay())
    ->whereHas('assets',function($query){
        $query->active();//is_delete=0
    })
    ->whereHas('assets',function($query) use($stext){
        $query->where('asset_reference','like',"%{$stext}%")
                ->orWhere('asset_detail','like',"%{$stext}%");

    })
    ->orWhereHas('assets.assetCategory',function($query) use($stext){
        $query->where('assets_category.name','like',"%{$stext}%");
    })
    ->orWhere('maintenance_due_date','like',"%{$stext}%")
    ->orWhere('maintenance_cost','like',"%{$stext}%")
    ->orderBy('maintenance_due_date','ASC')
    // ->toSql();
    ->paginate(10);

But its not giving me the correct results.Sometimes its giving me the record of another asset or inspector.I want result where inspector_id=?, manitenance_due_date is greater from now and assets is active means not deleted.

Other conditions are optional asset_reference,asset_detail,assets_category.name, maintenance_due_date, maintenance_cost

Add code of your two classes where you wrote relationship methods — ata, Mar 08 '19 at 10:25
I have added images, Ist for asset table and 2nd at bottom for asset_maintenance_table,please have a look — Anuj kumar, Mar 08 '19 at 10:37
Do you mean An Asset has Many asset_maintenance and assetManintenace belongs to Asset? Your question is not very clear. — pseudoanime, Mar 08 '19 at 12:29

score 0 · Answer 1 · answered Mar 08 '19 at 12:30

Try this

$data['reports'] = AssetMaintenance::whereHas('assets',function($query) use($stext){
     $query->active()  //is_delete=0
     $query->where('asset_reference','like',"%{$stext}%")
            ->orWhere('asset_detail','like',"%{$stext}%");
})
->where('inspector_id',$tenant_inspector_id)
->where('maintenance_due_date','>=',Carbon::now()->startOfDay())
->orWhereHas('assets.assetCategory',function($query) use($stext){
    $query->where('assets_category.name','like',"%{$stext}%");
})
->orWhere('maintenance_due_date','like',"%{$stext}%")
->orWhere('maintenance_cost','like',"%{$stext}%")
->orderBy('maintenance_due_date','ASC')
// ->toSql();
->paginate(10);

score 0 · Accepted Answer · answered Mar 08 '19 at 15:29

This is a case of misunderstanding precedence, in languages && / AND will always be calculated before the || / OR. This means that as example a shorter version of your query inspector_id == x AND maintenance_due_date >= Now() AND maintenance_due_date like %text% OR maintenance_cost like %text%can be true if only maintenance cost is liketext`. Because the statement will be evaluated like so.

inspector_id == x AND maintenance_due_date >= Now() AND maintenance_due_date like %text%` OR maintenance_cost like %text%

convert equal statements into its values

false AND false AND false OR true

as AND has a higher precedence they get evaluated first.

false OR true

and lastly evaluated to true or in other cases you can find elements that is not linked to a specific inspector if some of the searches are true.

Wrapping your search logic in a sub where clause, will get the results you want.

$data['reports'] = AssetMaintenance::with(['assets'])
    ->where('inspector_id',$tenant_inspector_id)
    ->where('maintenance_due_date','>=',Carbon::now()->startOfDay())
    ->whereHas('assets',function($query){
        $query->active();//is_delete=0
    })

    //here is the extra where

    ->where(function($query){
        $query->whereHas('assets',function($query) use($stext){
        $query->where('asset_reference','like',"%{$stext}%")
                ->orWhere('asset_detail','like',"%{$stext}%");

        })
    ->orWhereHas('assets.assetCategory',function($query) use($stext){
        $query->where('assets_category.name','like',"%{$stext}%");
    })
    ->orWhere('maintenance_due_date','like',"%{$stext}%")
    ->orWhere('maintenance_cost','like',"%{$stext}%")
    ->orderBy('maintenance_due_date','ASC');
    })

    ->paginate(10);

This will now wrap the search related where statements so the correct precedence will be there, short example again: inspector_id == x AND (maintenance_due_date like %text% OR maintenance_cost like %text%)

Laravel eloquent relation search not working properly

2 Answers2