Laravel Eloquent 父子级嵌套HasMany关联搜索实现

开发API过程中需要支持前端对返回数据的搜索功能，通过laravel eloquent从数据库查询数据，模型间配置了HasMany关联关系，初始查询返回的JSON结构正常，示例如下：
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",
        }
    ]
}

需要实现父级模块与submenu嵌套关联数组同时支持搜索，具体需求为：

• 传入请求体"search": "lorem"时，返回包含匹配该关键词子菜单的父级模块，且submenu数组中仅保留匹配的子项，预期返回结果如下：

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

• 传入"search": "Dummy"时，返回MODULENAME匹配该关键词的父级模块，且该父级下所有子菜单全部返回，预期返回结果如下：

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

已尝试方案及问题 #

最初在控制器中将查询结果转为Laravel Collection后通过filter方法过滤，仅匹配了父级的MODULENAME字段，未达到预期效果，对应实现代码如下：

$search = $request->search;

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

基础查询代码如下：

$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;

后续尝试whereHas方案，仅能过滤出存在匹配子项的父级模块，但会返回该父级下所有子菜单数据，无法过滤出仅匹配搜索关键词的子项，不符合需求，实现代码如下：

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

该方案返回结果：

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

• 截至2022年7月22日仍未找到符合需求的解决方案

要满足双场景搜索规则，需要在查询阶段同时处理父级筛选、关联子菜单的条件加载，最后加集合兜底过滤即可，完整实现代码如下：

use Illuminate\Support\Str;

$search = $request->input('search');
// 构建基础查询
$baseQuery = NavModule::where('VALIDSTATUS', 1)
    ->where('MODULETYPE', 'MAIN')
    ->where('LEVELNUMBER', 1)
    ->select('ID', 'MODULE_ID', 'MODULENAME')
    ->orderBy('ORDERNUMBER', 'ASC');

if ($search) {
    // 第一步：筛选符合条件的父级：父级名称匹配 或 存在匹配关键词的子菜单
    $baseQuery->where(function($q) use ($search) {
        $q->where('MODULENAME', 'like', '%' . $search . '%')
            ->orWhereHas('submenu', function($subQ) use ($search) {
                $subQ->where('VALIDSTATUS', 1)
                    ->where('MODULENAME', 'like', '%' . $search . '%');
            });
    });

    // 第二步：按规则加载子菜单：父级匹配则返回全部子菜单，否则仅返回匹配的子项
    $baseQuery->with(['submenu' => function($subQ) use ($search) {
        $subQ->where('VALIDSTATUS', 1)
            ->select('ID', 'MODULE_ID', 'MODULENAME')
            ->where(function($cond) use ($search) {
                // 判断当前子菜单所属父级是否匹配搜索词，匹配则不加子菜单过滤条件
                $cond->whereRaw("EXISTS (
                    SELECT 1 FROM nav_modules as parent
                    WHERE parent.ID = submenu.MODULE_ID
                    AND parent.MODULENAME LIKE ?
                )", ['%' . $search . '%'])
                // 父级不匹配时，仅返回子菜单名称命中关键词的项
                ->orWhere('MODULENAME', 'like', '%' . $search . '%');
            });
    }]);
} else {
    // 无搜索参数时正常加载全部有效子菜单
    $baseQuery->with(['submenu' => function($q) {
        $q->where('VALIDSTATUS', 1)
            ->select('ID', 'MODULE_ID', 'MODULENAME');
    }]);
}

$data = $baseQuery->get();

// 第三步：集合兜底过滤，清理边界异常数据
if ($search) {
    $data = $data->filter(function($item) use ($search) {
        // 父级名称匹配直接保留
        if (Str::contains(strtolower($item->MODULENAME), strtolower($search))) {
            return true;
        }
        // 父级不匹配时，仅保留存在匹配子项的记录
        return $item->submenu->isNotEmpty();
    })->values();
}

注：whereRaw语句中的表名nav_modules请替换为项目中NavModule模型对应的实际表名；如果需要前缀匹配而非全模糊匹配，将代码中'%' . $search . '%'替换为$search . '%'即可，对应原逻辑中Str::startsWith的匹配规则。

内容的提问来源于stack exchange，提问作者Hafid Maulana