搜索功能在Laravel中返回不需要的结果 [英] Search function return unwanted results in Laravel

问题描述

我制作了看起来像在运行的搜索功能，而不是一个 where 子句.我希望结果不显示尚未发布的项目.

I have made search function which looks like is working instead one where clause. I want in the results to not show items which aren't published yet.

因此，即使满足搜索条件，如果项目在数据库中已 published = 0 ，也不应在页面上显示该项目.

So if item has published = 0 in database should not be shown on page even if is met the search conditions.

这是我的职责

public function search(Request $request)
{

    $searchText = strip_tags($request['q']);
    $seachLocation = strip_tags($request['l']);


    $columns =['alias','description'];

    $query = Item::select('*');

    $query->where( 'title', 'like', '%'.$searchText.'%');
    $query->where('published', 1);
    foreach( $columns as $column) {
        $query->orWhere( $column, 'like', '%'.$searchText.'%', '%'.$seachLocation.'%');
    }

    $query->orWhereHas('category',function( $query ) use (  $searchText ) {
        $query->where('title', 'like', '%'.$searchText.'%' );
    });

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

    $items = $query->paginate(5);
    $searchQuery = $searchText;
    $searchQueryLoc = $seachLocation;


    return view('search', compact('items','searchQuery','seachLocation'));
}

我添加的是

$query->where('published', 1);

应该具有这种条件，但是我仍然在页面上看到未发布的项目.为什么会这样?

which should carry this condition but I still see unpublished items on page. Why is that?

toSql返回

select * from `items` where `title` like ? and `published` = ? and (`alias` like ? or `description` like ?) or exists (select * from `categories` where `items`.`category_id` = `categories`.`id` and `title` like ?) or exists (select * from `countries` where `items`.`country_id` = `countries`.`id` and `name` like ?) limit 5 offset 0

推荐答案

打印出SQL，您将明白原因.SQL对OR周围的条件进行分组，这意味着分别对 condition 1 OR condition 2 AND condition 3 进行评估.

Print out the SQL and you'll see why. SQL groups conditions around the OR which means condition 1 OR condition 2 AND condition 3 are evaluated separately.

这就是为什么您要用括号嵌套条件，以便始终评估条件3的原因:

This is why you nest conditions with parentheses so condition 3 is always evaluated:

(条件1或条件2)和条件3

Laravel也是如此，嵌套的方式是使用回调:

The same goes for Laravel, the way you nest is with callbacks:

$query->where(function($q) use ($columns, $searchText, $seachLocation) {
     foreach( $columns as $column) {
         $q->orWhere( $column, 'like', '%'.$searchText.'%', '%'.$seachLocation.'%');
     }
}

您始终可以使用 $ query-> toSql()来查看所生成SQL的表示形式.

You can always use $query->toSql() to see a representation of the generated SQL.

这篇关于搜索功能在Laravel中返回不需要的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！