Laravel中如何按搜索词search出现次数对查询的产品结果排序

你可以直接使用 Laravel 查询构造器的 selectRaw 方法注入自定义的计数逻辑，同时保留原有筛选条件，完整实现代码如下：

$searchTerm = $search;
$searchTermWithDash = str_replace(' ', '-', $search);

$products = Product::selectRaw('*,
    (LENGTH(`name`) - LENGTH(REPLACE(`name`, ?, ""))) / LENGTH(?) as appears_in_text,
    (LENGTH(`features`) - LENGTH(REPLACE(`features`, ?, ""))) / LENGTH(?) as appears_in_subject,
    (LENGTH(CONCAT(`name`," ",`features`)) - LENGTH(REPLACE(CONCAT(`name`," ",`features`), ?, ""))) / LENGTH(?) as occurences',
    [$searchTerm, $searchTerm, $searchTerm, $searchTerm, $searchTerm, $searchTerm]
)
->where('name', 'LIKE', "%{$searchTerm}%")
->orWhere('name', 'LIKE', "%{$searchTermWithDash}%")
->orWhere('features', 'LIKE', "%{$searchTerm}%")
->orderBy('occurences', 'DESC')
->get();

注意事项 #

• 所有动态搜索词都通过参数绑定传入，不会产生SQL注入风险
• 完全保留你原有三个筛选逻辑，不会改变筛选结果的范围
• 如果你不需要appears_in_text和appears_in_subject两个中间统计字段，可以简化selectRaw内容，只保留occurences的计算逻辑即可

内容的提问来源于stack exchange，提问作者Abdo Rabah