php - laravel如何拼接多个模糊条件查询?

Question

假如现在我接收到前端传来的表单数组:
$input = $request->input();
里面包含4个值:email,姓名,年龄,性别.现在需要根据用户输入的值进行数据表的查询,如果用户输入了email和姓名,则进行模糊查询,如果输入了年龄和性别,则进行准确查询.
可是问题来了,这4个input,用户可能填写一个,也可能填写4个,那我使用查询构造器的时候,不可能判断每个值是否存在,然后写构造器吧,毕竟模糊查询跟准确查询的写法不同,而且比如现在我有两个字段(email和姓名)是需要模糊查询的,laravel进行模糊查询的构造器是这样写的:
->where('email', 'LIKE', $input['email'])
那我必须在程序中判断email和姓名是否存在的情况,然后来拼接查询构造器吗?
这个思路是:

1.email存在,姓名不存在
->where('email', 'LIKE', $input['email'])
2.email不存在,姓名存在
->where('name', 'LIKE', $input['name'])
3.都存在
->where('email', 'LIKE', $input['email'])->->where('name', 'LIKE', $input['name'])

这些的代码量也太大了,如果我有十个字段模糊查询,那根本无法使用构造器了.
请问大家是不是有一种其他的方式,比如:

if($input['email']) $where['email'] = $input['email']
if($input['name'])  $where['name']  = $input['name']

然后使用:
->where( 'LIKE', $where),这样我一行就包括了所有可能存在也可能不存在的情况,
网上推荐过其他写法:
$where['email'] = ['like', $input['email']],但是我的laravel5.1并不能用.
请问大家有什么好的方法解决多条件模糊查询和非模糊查询吗?

Answer 1

Laravel can pass anonymous functions in where.

You can judge in this anonymous function.

For example, like this

where(function ($query) use ($request) {
    $request->input('email') && $query->where('email', $request->input('email'));
    $request->input('name') && $query->where('name', $request->input('name'));
})

This way you can make appropriate judgments without destroying the constructor structure.

Answer 2

I have never used 5.1, but 5.4 has a when method, example:

$query->when($name, function($query) use ($name){
    $query->where('name', 'like', '%' . $name . '%');
});

It means that when $name is not empty, the following function

will be executed.

Please check if there is a similar method in 5.1

Answer 3

Speaking of the query builderwhere()方法是返回$this--so you can write it in succession, but it doesn't say that you have to write it in succession!

When there are many fields, is it OK to write a for loop?:

$query = XXXModel::query();

for ($field in ['name', 'email', "... 你相加什么字段加什么字段"]){
    if ($fieldValue = $request->input($field)){
        $query->where($field, 'LIKE', "%$fieldValue%");
    }
}

// 继续添加where条件，或者查询数据...

Answer 4

$query = DB::table($table_name);
foreach ($conditions as $condition) {
    $query->where($condition, 'LIKE' $input($ocndition);
}
$query->get();

Answer 5

You must make a judgment, whether you make the judgment or the framework makes the judgment. If there are 10 fields for fuzzy query, can your mysql handle it? Just use a search engine instead