CODEWITHSUNDEEP
phplaravel
user7956165
user7956165

Reputation:

Search function return unwanted results in Laravel

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.

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

This is my function

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'));
}

What I've added is

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

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

toSql return

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

Upvotes: 1

Views: 112

Answers (1)

Devon Bessemer
Devon Bessemer

Reputation: 35337

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.

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

(condition 1 OR condition 2) AND condition 3

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.'%');
     }
}

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

Upvotes: 2

Related Questions