Search on nested Array Laravel Collection-CodePudding

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. 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

CodePudding user response：

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 . '%')
})

CodePudding user response：

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();