有条件地将where子句添加到eloquent查询中

问题描述：

I have a products database. I allow users to enter a search term and I display products related to their search term. I'm wanting to add the ability for the user to further filter these results by "gender" using AJAX. If the user selects a gender from a select box this is sent to the URL query string.

here is my code responsible for detecting if "gender" is in the query string.

$searchTerm = request('s');
$gender = request('gender');

$productsQuery = DB::table('products')
                ->where('title', 'LIKE', "%$searchTerm%");
if ($gender != "") {
    $productsQuery->where('gender', '=', "%$gender%");
}

$productsQuery->get();

When this method is called. I receive an empty collection.

我有一个产品数据库。我允许用户输入搜索字词，并显示与其搜索字词相关的产品。我想为用户添加使用AJAX进一步按“性别”过滤这些结果的功能。如果用户从选择框中选择性别，则将其发送到URL查询字符串。 p>

这是我的代码，负责检测查询字符串中是否有“性别”。 p>

  $ searchTerm = request（'s'  ）; 
 $ gender = request（'gender'）; 
 
 $ productsQuery = DB :: table（'products'）
  - ＆gt; where（'title'，'LIKE'，“％$ searchTerm％  “）; 
if（$ gender！=”“）{
 $ productsQuery-＆gt; where（'gender'，'='，”％$ gender％“）; 
} 
 
 $ productsQuery-＆gt  ;得到（）;  
  code>  pre> 
 
 调用此方法时。 我收到一个空集合。   p> 
  div>

答

Actually you can do this in one query. Just like that:

$productsQuery = DB::table('products')
->where('title', 'LIKE', '%'.$searchTerm.'%')
->where(function($query) use ($gender){
     if($gender != ""){
        $query->where('gender', '=', $gender);
      }
})
->get();

答

You should use LIKE also in the second where clause:

$productsQuery->where('gender', 'LIKE', "%{$gender%}");

EDITED

If you need to search for the exact gender you must not use wildcards at all.

$productsQuery->where('gender', '=', $gender);

答

Your idea and approach is correct, but you are mixing LIKE and = queries. % is a wildcard, but that can be only be used with LIKE.

So, if you want to use a wildcard and LIKE,

if(!empty($gender)) {
    $productsQuery->where('gender', 'LIKE', "%$gender%");
}

Or if you want to match by exact text,

if(!empty($gender)) {
    $productsQuery->where('gender', $gender);
}

Your current code will search for the exact match of the literal string %male%, for example.

Keep in mind that by using a wildcard, male will also match female (as they both contain the word "male").

有条件地将where子句添加到eloquent查询中

相关推荐