我正试图用laravel构建一个搜索表单,但我无法使用where子句
$term = $request->input('term');
$count = DB::table('members as m')
->where(DB::raw('m.member_first_name'), 'LIKE', "%$term%")
->orWhere(DB::raw('m.member_last_name'), 'LIKE', "%$term%")
->orWhere(DB::raw('m.member_business_address'), 'LIKE', "%$term%")
->orWhere(DB::raw("concat('m.member_first_name',' ','m.member_last_name')"), 'LIKE', "%$term%")
->orWhere(DB::raw("concat('m.member_last_name',' ','m.member_first_name')"), 'LIKE', "%$term%")
->orWhere(DB::raw("concat('m.member_name_affix',' ','m.member_last_name',' ','m.member_first_name')"), 'LIKE', "%$term%")
->orWhere(DB::raw("concat('m.member_first_name',' ','m.member_name_affix',' ','m.member_last_name')"), 'LIKE', "%$term%")
->count();
var_dump($count);
var_dump($count)始终返回所有数据库条目,无论搜索词是什么。
这是我的第一个Laravel项目,我将非常感谢任何形式的帮助。
也许可以尝试将查询封装在一个整体where中:
$count = DB::table('members as m')
->where(function ($q) {
$q->where(DB::raw('m.member_first_name'), 'LIKE', "%$term%")
->orWhere(DB::raw('m.member_last_name'), 'LIKE', "%$term%")
->orWhere(DB::raw('m.member_business_address'), 'LIKE', "%$term%")
->orWhere(DB::raw("concat('m.member_first_name',' ','m.member_last_name')"), 'LIKE', "%$term%")
->orWhere(DB::raw("concat('m.member_last_name',' ','m.member_first_name')"), 'LIKE', "%$term%")
->orWhere(DB::raw("concat('m.member_name_affix',' ','m.member_last_name',' ','m.member_first_name')"), 'LIKE', "%$term%")
->orWhere(DB::raw("concat('m.member_first_name',' ','m.member_name_affix',' ','m.member_last_name')"), 'LIKE', "%$term%")
})
->count();
您同时使用和/或,这当然会失败。尝试以下操作:
$count = DB::table('members as m')
->where(DB::raw('m.member_first_name'), 'LIKE', "%$term%")
->where(function ($query) {
$query->where(DB::raw('m.member_last_name'), 'LIKE', "%$term%")
->orWhere(DB::raw('m.member_business_address'), 'LIKE', "%$term%"));
})
只需尝试此代码,如果出现语法错误,请尝试更改'
符号。。因为我不能在发布之前测试它。。
$term = "%".$request->input('term')."%";
$count = DB::table('members as m')
->where('m.member_first_name', 'LIKE', $term)
->orWhere('m.member_last_name', 'LIKE', $term)
->orWhere('m.member_business_address', 'LIKE', $term)
->orWhereRaw('(concat_ws(m.member_first_name,m.member_last_name) LIKE ? )',[$term])
->orWhereRaw('(concat_ws(m.member_last_name,m.member_first_name) LIKE ? )',[$term])
->orWhereRaw('(concat_ws(m.member_name_affix,m.member_last_name,m.member_first_name) LIKE ? )',[$term])
->orWhereRaw('(concat_ws(m.member_first_name,m.member_name_affix,m.member_last_name) LIKE ? )',[$term])
->count();
var_dump($count);
您可以使用whereRaw()
,而不需要将DB::raw()
放在where()
中。