Search on nested Array Laravel Eloquent

Question

So I build my API and I have some data, and the front end make this data searchable. I get my data from database using laravel eloquent, and make HasMany Relation and get JSON result like this. The data is well.

JSON

{
    "ID": 444,
    "MODULE_ID": 1112,
    "MODULENAME": "Dashboard",
    "submenu": [
        {
            "ID": 1052,
            "MODULE_ID": 444,
            "MODULENAME": "Map Monitoring",
        },
        {
            "ID": 1053,
            "MODULE_ID": 444,
            "MODULENAME": "Map Status Terminal",
        }
    ]
},
{
    "ID": 445,
    "MODULE_ID": 1112,
    "MODULENAME": "Dummy",
    "submenu": [
        {
            "ID": 1055,
            "MODULE_ID": 445,
            "MODULENAME": "Dolor",
        },
        {
            "ID": 1056,
            "MODULE_ID": 445,
            "MODULENAME": "Lorem Ipsum",
        }
    ]
}

The Nested array on submenu relation array is searchable. The problem is, i want both the parent and child array searchable. if I set "search": "lorem" body in postman, the result should be like this:

[
    {
        "ID": 445,
        "MODULE_ID": 1112,
        "MODULENAME": "Dummy",
        "submenu": [
            {
                "ID": 1056,
                "MODULE_ID": 445,
                "MODULENAME": "Lorem Ipsum",
            }
        ]
    }
]

Or if "search": "Dummy", all data Dummy will show, like this:

[
    {
        "ID": 445,
        "MODULE_ID": 1112,
        "MODULENAME": "Dummy",
        "submenu": [
            {
                "ID": 1055,
                "MODULE_ID": 445,
                "MODULENAME": "Dolor",
            },
            {
                "ID": 1056,
                "MODULE_ID": 445,
                "MODULENAME": "Lorem Ipsum",
            }
        ]
    }
]

I try to convert to Laravel Collection on my Controller, but it's doesn't work. The result is same:

$search = $request->search;

if(isset($search)) {
    $data = collect($query)->filter(function($item) use ($search) {
        return Str::startsWith($item->MODULENAME, $search);
    });
}

My Query

$query = NavModule::where('VALIDSTATUS', 1)
    ->with(['submenu' => function($q) use ($request) {
        $q->where('VALIDSTATUS', 1);
        $q->select('ID', 'MODULE_ID', 'MODULENAME');
    }])
    ->where('MODULETYPE', 'MAIN')
    ->where('LEVELNUMBER', 1)
    ->where('VALIDSTATUS', 1)
    ->select('ID', 'MODULE_ID', 'MODULENAME')
    ->orderBy('ORDERNUMBER', 'ASC')
    ->get();

$search = $request->search;

if(isset($search)) {
    $data = collect($query)->filter(function($item) use ($search) {
        return Str::startsWith($item->MODULENAME, $search);
    });
}

$data = $query;

** UPDATE **

I try @mrhn option, but it's not result that I want.

NavModule::whereHas('submenu', function ($query) use ($request) {
    $query->where('MODULENAME', 'like', '%' . $request->search . '%')
})->with('submenu')

result

[
    {
        "ID": 445,
        "MODULE_ID": 1112,
        "MODULENAME": "Dummy",
        "submenu": [
            {
                "ID": 1055,
                "MODULE_ID": 445,
                "MODULENAME": "Dolor",
            },
            {
                "ID": 1056,
                "MODULE_ID": 445,
                "MODULENAME": "Lorem Ipsum",
            }
        ]
    }
]

It's return the One main array only, but return all the nested array data. I just want to return only 1 match data. I add ->with('submenu') because Laravel not return relation array if not add it.

I'll appreciate every answer and option from this forum. Thank you

update 22 july 2022

still haven't found the answer

user_2760986 · Answer

I would assume you can just use whereHas() to achieve what you want. This function can query relationships, remember to add your current queries to my example.

NavModule::whereHas('submenu', function ($query) use ($request) {
    $query->where('MODULENAME', 'like', '%' . $request->search . '%')
})

user_5582655 · Answer

you can use where condition on relation methods, you can use like bellow code

in your Menu model


public function subMenus():HasMany

{
 
  return $this->hasMany(SubMenu::class)->where('condition_key','condition_value')

}

//using 

Menus::with('subMenus')->get();

or you can more where method



Menus::with(['subMenus'=>fn($q)=>$q->where('condition_field','condition_value')
->where('fo','bar'))->get();

you can create more methods according to status for example


public function subMenusWithfoo():HasMany

{
 
  return $this->hasMany(SubMenu::class)
    ->where('condition_key','condition_value')
    ->where('bar','foo');

}

//using

Menu::with('subMenusWithfoo')->get();

user_9786205 · Answer

Thank you for all answer and comment. I have solve this problem. Here my code:

Controller

////////////////////
NavModule::where(fn ($query) =>
                $query->where('MODULENAME', 'like', '%' . $request->search . '%')
                ->where('VALIDSTATUS', '1')
                ->orderBy('ORDERNUMBER', 'ASC')
                    ->orWhereHas(
                        'submenu', fn ($subquery) =>
                            $subquery
        ->where('MODULENAME', 'LIKE', '%' . $request->search . '%')
     )                
   )   
  ->with('submenu')
  ->where('MODULETYPE', 'MAIN')
  ->where('LEVELNUMBER', 1)
  ->where('VALIDSTATUS', 1)
  ->select('ID', 'MODULE_ID', 'MODULENAME')
  ->orderBy('ORDERNUMBER', 'ASC')
  ->get();
//////////////// Rest of code

Model

public function submenu()
    {
        $search = request('search') ? : ""; 

        return $this->hasMany(NavModuleSub::class, 'MODULE_ID','ID')->where('MODULENAME', 'LIKE', '%' . $search . '%');
    }

Search on nested Array Laravel Eloquent

3 Answers