Laravel:高级搜索表单查询 [英] Laravel : advanced search form query

问题描述

我有一个高级搜索表单，可以使用Laravel从数据库中过滤出结果.数据已正确过滤，但我要求用户能够使用同一文本框(以高级格式)按名字或姓氏进行过滤.我尝试使用orWhere来确保它用名字或姓氏过滤名称字段，但是orWhere不考虑其他过滤器.我正在使用的代码如下:

I have an advanced search form to filter out results from a database using Laravel. The data is filtered correctly but I have a requirement for the user to be able to filter by first name or last name using the same text box (in the advanced form). I tried orWhere to make sure it filters the name field with the first name or last name but the orWhere doesn't consider the other filters. The code I am using is as follows:

DB::table('mytable')
                ->where(function($query) use ($name, $degree_s, $specialty_s, $city_s, $state_s, $lundbeck_id_s) {

                if ($name)
                    $query->where('first_name', 'like', "$name%")->orWhere('last_name', 'like', "$name%"); # this is whats causing the issue
                if ($specialty_s)
                    $query->where('primary_specialty', $specialty_s);
                if ($city_s)
                    $query->where('city', $city_s);
                if ($state_s)
                    $query->where('state_province', $state_s);
                if ($lundbeck_id_s)
                    $query->where('customer_master_id', $lundbeck_id_s);
                if ($degree_s)
                    $query->where('primary_degree', $degree_s);
                })

                ->select('id', 'first_name','last_name')

添加orWhere子句会使查询也不使用其他条件(例如city_s或state_s).

Adding the orWhere clause causes the query to not use the other conditions as well (like city_s or state_s).

推荐答案

您需要更改:

if ($name)
   $query->where('first_name', 'like', "$name%")->orWhere('last_name', 'like', "$name%");

进入:

if ($name) {
   $query->where(function($q) use ($name) {
         $q->where('first_name', 'like', "$name%")->orWhere('last_name', 'like', "$name%");
   });
}

使Laravel添加括号，以便它可以按您期望的方式工作.

to make Laravel to add parentheses so it will work as you expect.

编辑

当然，您无需在此处使用闭包将所有内容包装起来，因此，最好的解决方案是:

Of course you don't need to wrap everything with closure here, so the best solution for that would be:

<?php

$query = DB::table('mytable')->select('id', 'first_name', 'last_name');

if ($name) {
    $query->where(function ($q) use ($name) {
        $q->where('first_name', 'like', "$name%")
            ->orWhere('last_name', 'like', "$name%");
    });
}
if ($specialty_s) {
    $query->where('primary_specialty', $specialty_s);
}
if ($city_s) {
    $query->where('city', $city_s);
}
if ($state_s) {
    $query->where('state_province', $state_s);
}
if ($lundbeck_id_s) {
    $query->where('customer_master_id', $lundbeck_id_s);
}
if ($degree_s) {
    $query->where('primary_degree', $degree_s);
}

$data = $query->get();

这篇关于Laravel:高级搜索表单查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！