Laravel 高级搜索查询修复

时间:2023-04-10
本文介绍了Laravel 高级搜索查询修复的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

我有一个包含多个输入和选择框的搜索表单,我需要帮助来获取查询中的条件,以便每个部分单独工作并同时工作.

这是我的刀片代码:

{{csrf_field()}}<div class="sidebar-title"><span>高级搜索</span><i class="fa fa-caret-down show_sidebar_content" aria-hidden="true"></i>

<!-- ./sidebar-title --><div id="tags-filter-content" class="sidebar-content"><div class="filter-tag-group">@foreach($options 作为 $option)<div class="tag-group"><p class="title"><span class="filter-title show_filter_content">{{$option->title}} <span class="pull-right"><i class="fa fa-minus"></i></span></span></p><div class="filter-content"><div class="checkbox">@foreach($option->suboptions as $suboption)<label for="suboptions"><input name="suboptions[]" type="checkbox" value="{{$suboption->id}}">{{ucfirst($suboption->title)}}@endforeach

@endforeach<!-- ./tag-group --><div class="tag-group"><p class="title"><span class="filter-title show_filter_content">品牌<span class="pull-right"><i class="fa fa-minus"></i></span><;/span></p><div class="filter-content"><div class="checkbox">@foreach($brands 作为 $brand)<label for="brands"><input name="brands[]" type="checkbox" value="{{$brand->id}}">{{$brand->title}}@endforeach

<!-- ./tag-group --><div class="tag-group"><p class="title"><span class="filter-title show_filter_content">价格范围<span class="pull-right"><i class="fa fa-minus"></i></span></span></p><div class="row filter-content"><div class="col-md-6"><div class="form-group"><label for="min_price" hidden>Min</label><input type="text" name="min_price" class="form-control" placeholder="Rp Min">

<div class="col-md-6"><div class="form-group"><label for="max_price" hidden>Max</label><input type="text" name="max_price" class="form-control" placeholder="Rp Max">

<!-- 标签组--><div class="text-center mt-20"><button type="submit" class="btn btn-danger">TERPAKAN</button>

</div><!-- ./filter-tag-group --></div><!-- ./sidebar-content --></表单>

这是我的路线:

Route::post('/advanced-search', 'frontendSearchController@filter')->name('advancesearch');

最后我的函数代码是:

公共函数advancedsearch(Request $request) {$brands = Brand::all();//用于页面的其他部分.(与搜索功能无关)$options = Option::all();//用于页面的其他部分.(与搜索功能无关)$suboptions = DB::table('product_suboption');//我的 product_id 和 subopyion_id 保存在哪里//搜索功能$products = Product::where(function($query){//获取输入$suboptions2 = Input::has('suboptions') ?Input::get('suboptions') : [];$min_price = Input::has('min_price') ?Input::get('min_price') : null;$max_price = Input::has('max_price') ?Input::get('max_price') : null;$brands2 = Input::has('brands') ?Input::get('brands') : [];//返回结果$query->where('price','>=',$min_price)->where('price','<=',$max_price);})->get();return view('front.advancesearch', compact('products', 'brands', 'options'));}

我的模特关系:

product 型号:

公共函数选项(){返回 $this->belongsToMany(Option::class);}公共功能子选项(){返回 $this->belongsToMany(Suboption::class, 'product_suboption', 'product_id', 'suboption_id');}公共功能品牌(){返回 $this->belongsTo(Brand::class);}

Option 模型:

公共函数子选项(){返回 $this->hasMany(Suboption::class, 'option_id');}公共功能产品(){返回 $this->belongsToMany(Product::class);}

子选项模型:

公共函数选项(){返回 $this->belongsTo(Option::class, 'option_id');}公共功能产品(){返回 $this->belongsToMany(Product::class);}

品牌型号:

公共功能产品(){返回 $this->hasMany(Product::class);}

注意

我的 brands 搜索来自产品表,其中每个产品都有 brand_id 列.

但是

我的 suboptions 来自名为 product_suboption 的第三个表(如您在我的模型代码中看到的),我在其中保存了 product_idsuboption_id.

解决方案

SOLVED

在玩了数周的代码之后,我终于为自己找到了正确的结果(在我的情况下,它对其他人有效,可能适用于其他建议的答案)

公共函数advancedsearch(Request $request) {$options = Option::all();$brands = Brand::all();$brandss = Input::has('brands') ?Input::get('brands') : [];$suboption = Input::has('suboptions') ?(int)Input::get('suboptions') : [];$min_price = Input::has('min_price') ?(int)Input::get('min_price') : null;$max_price = Input::has('max_price') ?(int)Input::get('max_price') : null;//代码if(count($request['suboptions'])){$products = DB::table('products')->join('product_suboption', function ($join) {$suboption = Input::has('suboptions') ?Input::get('suboptions') : [];$join->on('products.id', '=', 'product_suboption.product_id')-> where('product_suboption.suboption_id', '=', $suboption);})-> 分页(12);}elseif(count($request['brands'])){$products = DB::table('products')->whereIn('products.brand_id', $brandss)-> 分页(12);}elseif(count($request['min_price']) && count($request['max_price'])){$products = DB::table('products')->whereBetween('price', [$min_price, $max_price])-> 分页(12);}return view('front.advancesearch', compact('products', 'brands', 'options'));}

<块引用>

注意:我的大部分定价问题都是用 (int) 解决的,正如你在我的代码中看到的 (int)Input::get('min_price')(int)Input::get('max_price').

特别感谢 Ravindra Bhandericount($request[''] 建议.

I have a search form with multiple input and select boxes I need help to get if conditions in my query in order to each part works separately and all at once.

here is my blade codes:

<form action="{{route('advancesearch')}}" method="post">
      {{csrf_field()}}
      <div class="sidebar-title">
        <span>Advanced Search</span>
        <i class="fa fa-caret-down show_sidebar_content" aria-hidden="true"></i>
      </div>
      <!-- ./sidebar-title -->

      <div id="tags-filter-content" class="sidebar-content">
        <div class="filter-tag-group">

          @foreach($options as $option)
          <div class="tag-group">
            <p class="title">
              <span class="filter-title show_filter_content">{{$option->title}} <span class="pull-right"><i class="fa fa-minus"></i></span></span>
            </p>
            <div class="filter-content">
              <div class="checkbox">
              @foreach($option->suboptions as $suboption)
              <label for="suboptions">
                <input name="suboptions[]" type="checkbox" value="{{$suboption->id}}">
                {{ucfirst($suboption->title)}}
              </label>
              @endforeach
            </div>
          </div>
          </div>
          @endforeach
          <!-- ./tag-group -->

          <div class="tag-group">
            <p class="title">
              <span class="filter-title show_filter_content">Brand <span class="pull-right"><i class="fa fa-minus"></i></span></span>
            </p>
            <div class="filter-content">
              <div class="checkbox">
              @foreach($brands as $brand)
              <label for="brands">
                <input name="brands[]" type="checkbox" value="{{$brand->id}}">
                {{$brand->title}}
              </label>
              @endforeach
            </div>
          </div>
          </div>
          <!-- ./tag-group -->

          <div class="tag-group">
            <p class="title">
              <span class="filter-title show_filter_content">Price Range <span class="pull-right"><i class="fa fa-minus"></i></span></span>
            </p>
            <div class="row filter-content">
              <div class="col-md-6">
                <div class="form-group">
                  <label for="min_price" hidden>Min</label>
                  <input type="text" name="min_price" class="form-control" placeholder="Rp Min">
                </div>
              </div>
              <div class="col-md-6">
                <div class="form-group">
                  <label for="max_price" hidden>Max</label>
                  <input type="text" name="max_price" class="form-control" placeholder="Rp Max">
                </div>
              </div>
            </div>
          </div>
          <!-- tag-group -->

          <div class="text-center mt-20">
            <button type="submit" class="btn btn-danger">TERPAKAN</button>
          </div>

        </div><!-- ./filter-tag-group -->
      </div><!-- ./sidebar-content -->
    </form>

and this is my route:

Route::post('/advanced-search', 'frontendSearchController@filter')->name('advancesearch');

finally my function code is:

public function advancedsearch(Request $request) {
        $brands = Brand::all(); // uses for other part of the page. (not related to search function)
        $options = Option::all(); // uses for other part of the page. (not related to search function)
        $suboptions = DB::table('product_suboption'); // where my product_id and subopyion_id saves

        //search function
        $products = Product::where(function($query){
            //getting inputs
            $suboptions2 = Input::has('suboptions') ? Input::get('suboptions') : [];
            $min_price = Input::has('min_price') ? Input::get('min_price') : null;
            $max_price = Input::has('max_price') ? Input::get('max_price') : null;
            $brands2 = Input::has('brands') ? Input::get('brands') : [];

            //returning results
            $query->where('price','>=',$min_price)
                    ->where('price','<=',$max_price);
            })->get();

        return view('front.advancesearch', compact('products', 'brands', 'options'));
    }

My models relations:

product model:

public function options(){
     return $this->belongsToMany(Option::class);
  }
  public function suboptions(){
     return $this->belongsToMany(Suboption::class, 'product_suboption', 'product_id', 'suboption_id');
  }
public function brand(){
     return $this->belongsTo(Brand::class);
  }

Option model:

public function suboptions(){
     return $this->hasMany(Suboption::class, 'option_id');
  }

  public function products(){
     return $this->belongsToMany(Product::class);
  }

Suboption model:

public function option(){
     return $this->belongsTo(Option::class, 'option_id');
  }

  public function products(){
     return $this->belongsToMany(Product::class);
  }

Brand model:

public function products(){
     return $this->hasMany(Product::class);
}

note

My brands search is coming from products table where I have column brand_id for each product.

BUT

My suboptions come from 3rd table named product_suboption (as you see in my models codes) where i save product_id and suboption_id.

解决方案

SOLVED

After weeks of playing with codes finally I came to the right results for myself (in my case it works this way for others maybe works with other suggested answers)

public function advancedsearch(Request $request) {
    $options = Option::all();
    $brands = Brand::all();
    $brandss = Input::has('brands') ? Input::get('brands') : [];
    $suboption = Input::has('suboptions') ? (int)Input::get('suboptions') : [];
    $min_price = Input::has('min_price') ? (int)Input::get('min_price') : null;
    $max_price = Input::has('max_price') ? (int)Input::get('max_price') : null;

    //codes
    if(count($request['suboptions'])){
      $products = DB::table('products')
      ->join('product_suboption', function ($join) {
        $suboption = Input::has('suboptions') ? Input::get('suboptions') : [];
            $join->on('products.id', '=', 'product_suboption.product_id')
                 ->where('product_suboption.suboption_id', '=', $suboption);
        })
      ->paginate(12);
    }

    elseif(count($request['brands'])){
      $products = DB::table('products')
      ->whereIn('products.brand_id', $brandss)
      ->paginate(12);
    }

    elseif(count($request['min_price']) && count($request['max_price'])){
      $products = DB::table('products')
      ->whereBetween('price', [$min_price, $max_price])
      ->paginate(12);
    }


    return view('front.advancesearch', compact('products', 'brands', 'options'));
    }

NOTE: most of my pricing issues solved with (int) as you see in my codes (int)Input::get('min_price') and (int)Input::get('max_price').

Special thanks to Ravindra Bhanderi for his count($request[''] suggestion.

这篇关于Laravel 高级搜索查询修复的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

上一篇:雄辩的 laravel WhereIn All 下一篇:如何在 Laravel eloquent 中进行子查询?

相关文章