php - How to splice multiple fuzzy conditional queries in laravel?

Question

Suppose now I receive the form array from the front end:
$input = $request->input();
contains 4 values: email, name, age, gender. Now the data table needs to be queried based on the values ​​entered by the user. If the user enters email and name, it will be blurred Query, if age and gender are entered, an accurate query will be performed.
But here comes the problem. The user may fill in one of these four inputs. , or maybe 4. Then when I use the query constructor, it is impossible to judge whether each value exists, and then write the constructor. After all, fuzzy query and exact query are written differently, and for example, now I have two fields (email and name) require fuzzy query. Laravel's constructor for fuzzy query is written like this:
->where('email', 'LIKE', $input['email'])
Then I have to determine whether email and name exist in the program, and then splice the query constructor?
The idea is:

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'])

The amount of code is too large. If I have ten fields for fuzzy query, I can’t use the constructor at all.
Can you please tell me if there is another way, such as:

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

Then use:
->where( 'LIKE', $where), so that I include all situations that may or may not exist in one line.
Other writing methods are recommended on the Internet:
$where['email'] = ['like', $input['email']], but my laravel5.1 cannot be used.
Do you have any good methods to solve multi-condition fuzzy query and non-fuzzy query?

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